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

有一个SQL语句集合,里面可能有selectselect ... left joinupdatedeleteinsert

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

<?
function get__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 )


搬瓦工,CN2高速线路,1GB带宽,电信联通优化KVM,延迟低,速度快,建站稳定,搬瓦工BandwagonHost VPS优惠码BWH26FXH3HIQ,支持<支付宝> 【点击购买】!

Vultr$3.5日本节点,512M内存/500G流量/1G带宽,电信联通优化,延迟低,速度快【点击购买】!

腾讯云云产品精选秒杀【点击购买】

阿里云香港、新加坡VPS/1核/1G/25G SSD/1T流量/30M带宽/年付¥288【点击购买】

百度云不止一折优惠【点击购买】

发表评论

电子邮件地址不会被公开。 必填项已用*标注