是否有一个用 PHP 编写的轻量级 sql 解析器类可以做到这一点？

Question

Closed. This question is seeking recommendations for software libraries, tutorials, tools, books, or other off-site resources. It does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

我们不允许提出寻求软件库、教程、工具、书籍或其他场外资源推荐的问题。您可以编辑问题，以便用事实和引文来回答。

6 年前已关闭。

Answer 1

PHP SQL 解析器可能正是您要找的。正如您从链接中看到的，它将处理相当复杂的查询。从项目首页下载代码。唯一的缺点是它仅针对 MySQL。添加对 PostgreSQL 的支持应该不是什么大问题。

SQL解析还有一个更基本的解决方案：PHP SQL Tokenizer，但它确实除了选择/来自/哪里/顺序分离之外，不为您提供任何内容：没有字段名称、子查询提取等。

Answer 2

你可以尝试cbMySQL，我不太了解，但这可能就是您正在寻找的东西。

Answer 3

我最近尝试这样做
PHP-Light-SQL-Parser-Class 这更轻其他类

<?php
/**
 * Light SQL Parser Class
 * @author Marco Cesarato <[email protected]>
 * @copyright Copyright (c) 2018
 * @license http://opensource.org/licenses/gpl-3.0.html GNU Public License
 * @link https://github.com/marcocesarato/PHP-Light-SQL-Parser-Class
 * @version 0.1.86
 */
class LightSQLParser {
    // Public
    public $query = '';
    // Private
    protected static $connectors = array('OR', 'AND', 'ON', 'LIMIT', 'WHERE', 'JOIN', 'GROUP', 'ORDER', 'OPTION', 'LEFT', 'INNER', 'RIGHT', 'OUTER', 'SET', 'HAVING', 'VALUES', 'SELECT', '\(', '\)');
    protected static $connectors_imploded = '';
    /**
     * Constructor
     */
    public function __construct($query = '') {
        $this->query = $query;
        if(empty(self::$connectors_imploded))
            self::$connectors_imploded = implode('|', self::$connectors);
        return $this;
    }
    /**
     * Set SQL Query string
     */
    public function setQuery($query) {
        $this->query = $query;
        return $this;
    }
    /**
     * Get SQL Query method
     * @param $query
     * @return string
     */
    public function method($query = null){
        $methods = array('SELECT','INSERT','UPDATE','DELETE','RENAME','SHOW','SET','DROP','CREATE INDEX','CREATE TABLE','EXPLAIN','DESCRIBE','TRUNCATE','ALTER');
        $queries = empty($query) ? $this->_queries() : array($query);
        foreach($queries as $query){
            foreach($methods as $method) {
                $_method = str_replace(' ', '[\s]+', $method);
                if(preg_match('#^[\s]*'.$_method.'[\s]+#i', $query)){
                    return $method;
                }
            }
        }
        return '';
    }
    /**
     * Get Query fields (at the moment only SELECT/INSERT/UPDATE)
     * @param $query
     * @return array
     */
    public function fields(){
        $fields = array();
        $queries = $this->_queries();
        foreach($queries as $query) {
            $method = $this->method($query);
            switch ($method){
                case 'SELECT':
                    preg_match('#SELECT[\s]+([\S\s]*)[\s]+FROM#i', $query, $matches);
                    if (!empty($matches[1])) {
                        $match = trim($matches[1]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]+(AS[\s]+)?[\w\.]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    }
                    break;
                case 'INSERT':
                    preg_match('#INSERT[\s]+INTO[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)\(([\S\s]*)\)[\s]+VALUES#i', $query, $matches);
                    if (!empty($matches[4])) {
                        $match = trim($matches[4]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]+(AS[\s]+)?[\w\.]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    } else {
                        preg_match('#INSERT[\s]+INTO[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)SET([\S\s]*)([\;])?#i', $query, $matches);
                        if (!empty($matches[4])) {
                            $match = trim($matches[4]);
                            $match = explode(',', $match);
                            foreach ($match as $field) {
                                $field = preg_replace('#([\s]*\=[\s]*[\S\s]+)#i', '', trim($field));
                                $fields[] = $field;
                            }
                        }
                    }
                    break;
                case 'UPDATE':
                    preg_match('#UPDATE[\s]+([\w\.]+([\s]+(AS[\s]+)?[\w\.]+)?[\s]*)SET([\S\s]*)([\s]+WHERE|[\;])?#i', $query, $matches);
                    if (!empty($matches[4])) {
                        $match = trim($matches[4]);
                        $match = explode(',', $match);
                        foreach ($match as $field) {
                            $field = preg_replace('#([\s]*\=[\s]*[\S\s]+)#i', '', trim($field));
                            $fields[] = $field;
                        }
                    }
                    break;
                case 'CREATE TABLE':
                    preg_match('#CREATE[\s]+TABLE[\s]+\w+[\s]+\(([\S\s]*)\)#i', $query, $matches);
                    if (!empty($matches[1])) {
                        $match = trim($matches[1]);
                        $match = explode(',', $match);
                        foreach ($match as $_field) {
                            preg_match('#^w+#', trim($_field), $field);
                            if (!empty($field[0])) {
                                $fields[] = $field[0];
                            }
                        }
                    }
                    break;
            }
        }
        return array_unique($fields);
    }
    /**
     * Get SQL Query First Table
     * @param $query
     * @return string
     */
    public function table(){
        $tables = $this->tables();
        return $tables[0];
    }
    /**
     * Get SQL Query Tables
     * @return array
     */
    function tables(){
        $results = array();
        $queries = $this->_queries();
        foreach($queries as $query) {
            $patterns = array(
                '#[\s]+FROM[\s]+(([\s]*(?!'.self::$connectors_imploded.')[\w]+([\s]+(AS[\s]+)?(?!'.self::$connectors_imploded.')[\w]+)?[\s]*[,]?)+)#i',
                '#[\s]*INSERT[\s]+INTO[\s]+([\w]+)#i',
                '#[\s]*UPDATE[\s]+([\w]+)#i',
                '#[\s]+[\s]+JOIN[\s]+([\w]+)#i',
                '#[\s]+TABLE[\s]+([\w]+)#i'
            );
            foreach($patterns as $pattern){
                preg_match_all($pattern,$query, $matches, PREG_SET_ORDER);
                foreach ($matches as $val) {
                    $tables = explode(',', $val[1]);
                    foreach ($tables as $table) {
                        $table = trim(preg_replace('#[\s]+(AS[\s]+)[\w\.]+#i', '', $table));
                        $results[] = $table;
                    }
                }
            }
        }
        return array_unique($results);
    }
    /**
     * Get all queries
     * @return array
     */
    protected function _queries(){
        $queries = preg_replace('#\/\*[\s\S]*?\*\/#','', $this->query);
        $queries = preg_replace('#;(?:(?<=["\'];)|(?=["\']))#', '', $queries);
        $queries = preg_replace('#[\s]*UNION([\s]+ALL)?[\s]*#', ';', $queries);
        $queries = explode(';', $queries);
        return $queries;
    }
}

使用示例

header("Content-Type: text/plain"); 

echo '========= Light SQL Parser DEMO =========' . PHP_EOL; 

echo PHP_EOL . '### UPDATE ###' . PHP_EOL; 

$lsp = new LightSQLParser("UPDATE Customers as ae 
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' 
WHERE CustomerID = 1;"); 

// OR 

/* 
$lsp = new LightSQLParser(); 
$lsp->setQuery("UPDATE Customers as ae 
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' 
WHERE CustomerID = 1;"); 
*/ 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields()); 

echo PHP_EOL . '### SELECT ###' . PHP_EOL; 

$lsp->setQuery("SELECT surname, given_names, title FROM Person 
  JOIN Author on person.ID = Author.personID 
  JOIN Book on Book.ID = Author.publicationID 
UNION ALL 
SELECT surname, given_names, title FROM Person 
  JOIN Author on person.ID = Author.personID 
  JOIN Article on Article.ID = Author.publicationID"); 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields()); 

echo PHP_EOL . '### INSERT ###' . PHP_EOL; 

$lsp->setQuery("INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) 
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');"); 

echo PHP_EOL . 'METHOD' . PHP_EOL; 
var_dump($lsp->method()); 

echo PHP_EOL . 'TABLES' . PHP_EOL; 
var_dump($lsp->tables()); 

echo PHP_EOL . 'FIELDS' . PHP_EOL; 
var_dump($lsp->fields());