老季博客

标签： SQL

  • PHP如何正则表达式获取SQL语句中的表名？

    PHP如何正则表达式获取SQL语句中的表名？

    有一个SQL语句集合，里面可能有select、select ... left join、update、delete、insert。

    要从这语句集合中获取到所有的表名，代码如下:

    <?php
function get_sql_tables($sqlString) {
    $sqlString = str_replace( '`','',trim($sqlString) ).' AND 1=1 ';
    $key = strtolower(substr($sqlString, 0, 6));
    if( $key === 'select' ){
        $tmp = explode('where' , strtolower( trim( $sqlString ) ) );
        $tmp = explode('from',$tmp[0]);
        if ( strpos($tmp[1],',') !== false && ! stristr( $tmp[1],'select')){
            $tmp = explode( ',' , $tmp[1] );
            foreach( $tmp as $k => $v ){
                $v = trim( $v );
                if( strpos( $v , ' ') !== false ){
                    $tv = explode(' ' , $v);
                    $return[] = $tv[0];
                }
            }
            return $return;
        }else{
            $expression = '/((SELECT.+?FROM)|(LEFT\\s+JOIN|JOIN|LEFT))[\\s`]+?(\\w+)[\\s`]+?/is';
        }
    }else if( $key === 'delete' ){
        $expression = '/DELETE\\s+?FROM[\\s`]+?(\\w+)[\\s`]+?/is';
    }else if( $key === 'insert' ){
        $expression = '/INSERT\\s+?INTO[\\s`]+?(\\w+)[\\s`]+?/is';
    }else if( $key === 'update' ){
        $tmp = explode( 'set' , strtolower( str_replace('`','',trim( $sqlString ) ) ) );
        $tmp = explode( 'update' , $tmp[0] );
        if ( strpos($tmp[1] , ',' ) !== false && ! stristr( $tmp[1] , 'update' ) ){
            $tmp = explode( ',' , $tmp[1] );
            foreach( $tmp as $k => $v ){
                $v = trim( $v );
                if( strpos( $v , ' ') !== false ){
                    $tv = explode(' ' , $v);
                    $return[] = $tv[0];
                }
            }
            return $return;
        }else{
            $expression = '/UPDATE[\\s`]+?(\\w+)[\\s`]+?/is';
        }
    }
    preg_match_all($expression, $sqlString, $matches);
    return array_unique(array_pop($matches));
}

    使用姿势：

    <?php
$sql = "select count(*) as count from order as a left join user as b on a.user_id = b.user_id where a.title like '%老季%'";
var_dump(get_sql_tables());#输出结果：order和user
$sql = "select * from T_5 a, T_6 b , T_7 c where a.id=b.id";
var_dump(get_sql_tables());#输出结果：Array ( [0] => T_5 [1] => T_6 [2] => T_7 )


WeChat
↑