Python/PHP MySQL语句解析器解决业务分表

citgpt 2024-09-26 11:31 8 浏览 0 评论

自己曾经做过一个网盘项目。刚开始由于需要快速地从0到1建设上线，所以没有对核心文档表进行分表。当然我的架构理念也是“按需架构设计”。产品需求在没有明确的长远计划的情况下以“小步快跑，赶超竞品”为主。后期由于产品功能触达目标用户群需求点、产品用户体验不断提升、产品多方位导流、加强产品推广文档表每天有百万数据增长量。不得不对文档表进行按用户id分表。当时产品功能已全覆盖文档的生命周期。产品功能已丰富多彩。修改所有关联文档表的业务代码为按用户id分表开发测试成本非常高。上线后线上问题不可控。经过考虑在业务代码最底层DB层进行SQL语句解析来进行用户id分表处理。这样的话开发测试成本都非常低。上线后有问题方便回滚和追查原因。

今天为大家介绍Python/PHP两种MySQL语句解析器。当时网盘项目用的是PHP编程语言开发。

Python的SQL语句解析器。个人推荐使用moz_sql_parser库。经调研官方的sqlparse库解析出来的语句段无法满足需求也很难理解。

1、Python moz_sql_parser库安装

pip install moz_sql_parser

2、Python moz_sql_parser SQL语句解析

from moz_sql_parser import parse
import json

#用例1
sql = 'select id,name from t1 where id > 1'
tree = parse(sql)
print(json.dumps(tree))
执行结果：
{
    "select": [
        {
            "value": "id"
        }, 
        {
            "value": "name"
        }
    ], 
    "from": "t1", 
    "where": {
        "gt": [
            "id", 
            1
        ]
    }
}

 #用例2
sql = 'select id,name from t1 where id > 1 and id < 1'
tree = parse(sql)
print(json.dumps(tree))
 执行结果：
 {
    "select": [
        {
            "value": "id"
        }, 
        {
            "value": "name"
        }
    ], 
    "from": "t1", 
    "where": {
        "and": [
            {
                "gt": [
                    "id", 
                    1
                ]
            }, 
            {
                "lt": [
                    "id", 
                    1
                ]
            }
        ]
    }
}

#用例3
sql = "select id,name from t1 where id in (select id from t2 WHERE content = 'xxxxxx')"
tree = parse(sql)
print(json.dumps(tree))
执行结果：
{
    "select": [
        {
            "value": "id"
        }, 
        {
            "value": "name"
        }
    ], 
    "from": "t1", 
    "where": {
        "in": [
            "id", 
            {
                "select": {
                    "value": "id"
                }, 
                "from": "t2", 
                "where": {
                    "eq": [
                        "content", 
                        {
                            "literal": "xxxxxx"
                        }
                    ]
                }
            }
        ]
    }
}

#用例4 
sql = "select t1.id, t1.name from t1 join t2 on t1.id = t2.id where t2.id > 1"
tree = parse(sql)
print(json.dumps(tree))
执行结果：
{
    "select": [
        {
            "value": "t1.id"
        }, 
        {
            "value": "t1.name"
        }
    ], 
    "from": [
        "t1", 
        {
            "join": "t2", 
            "on": {
                "eq": [
                    "t1.id", 
                    "t2.id"
                ]
            }
        }
    ], 
    "where": {
        "gt": [
            "t2.id", 
            1
        ]
    }
}

3、Python moz_sql_parser总结

moz_sql_parser解析出来的结果符合SQL语法格式。
moz_sql_parser解析出来的结果适合业务分表的需求（可以取到SQL语句里的表名和WHERE条件）
moz_sql_parser解析出来的结果代码二次开发复杂度低。

PHP的SQL语句解析器。个人推荐使用PhpMyAdmin的sql-parser组件。PhpMyAdmin是经过历史检验可信赖的。

1、PHP PhpMyAdmin/sql-parser安装

composer require phpmyadmin/sql-parser

2、PHP PhpMyAdmin/sql-parser SQL语句解析

<?php
require_once 'vendor/autoload.php';

use PhpMyAdmin\SqlParser\Parser;
$query = 'SELECT t1.id, t1.name FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.name = "xxxx"';
$parser = new Parser($query);
$stmt = $parser->statements[0];
var_dump($stmt);
执行结果：
object(PhpMyAdmin\SqlParser\Statements\SelectStatement)#46 (17) {
  ["expr"]=>
  array(2) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\Expression)#48 (7) {
      ["database"]=>
      NULL
      ["table"]=>
      string(2) "t1"
      ["column"]=>
      string(2) "id"
      ["expr"]=>
      string(5) "t1.id"
      ["alias"]=>
      NULL
      ["function"]=>
      NULL
      ["subquery"]=>
      NULL
    }
    [1]=>
    object(PhpMyAdmin\SqlParser\Components\Expression)#49 (7) {
      ["database"]=>
      NULL
      ["table"]=>
      string(2) "t1"
      ["column"]=>
      string(4) "name"
      ["expr"]=>
      string(7) "t1.name"
      ["alias"]=>
      NULL
      ["function"]=>
      NULL
      ["subquery"]=>
      NULL
    }
  }
  ["from"]=>
  array(1) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\Expression)#50 (7) {
      ["database"]=>
      NULL
      ["table"]=>
      string(2) "t1"
      ["column"]=>
      NULL
      ["expr"]=>
      string(2) "t1"
      ["alias"]=>
      NULL
      ["function"]=>
      NULL
      ["subquery"]=>
      NULL
    }
  }
  ["index_hints"]=>
  NULL
  ["partition"]=>
  NULL
  ["where"]=>
  array(1) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\Condition)#54 (3) {
      ["identifiers"]=>
      array(3) {
        [0]=>
        string(2) "t2"
        [1]=>
        string(4) "name"
        [2]=>
        string(4) "xxxx"
      }
      ["isOperator"]=>
      bool(false)
      ["expr"]=>
      string(16) "t2.name = "xxxx""
    }
  }
  ["group"]=>
  NULL
  ["having"]=>
  NULL
  ["order"]=>
  NULL
  ["limit"]=>
  NULL
  ["procedure"]=>
  NULL
  ["into"]=>
  NULL
  ["join"]=>
  array(1) {
    [0]=>
    object(PhpMyAdmin\SqlParser\Components\JoinKeyword)#51 (4) {
      ["type"]=>
      string(4) "LEFT"
      ["expr"]=>
      object(PhpMyAdmin\SqlParser\Components\Expression)#52 (7) {
        ["database"]=>
        NULL
        ["table"]=>
        string(2) "t2"
        ["column"]=>
        NULL
        ["expr"]=>
        string(2) "t2"
        ["alias"]=>
        NULL
        ["function"]=>
        NULL
        ["subquery"]=>
        NULL
      }
      ["on"]=>
      array(1) {
        [0]=>
        object(PhpMyAdmin\SqlParser\Components\Condition)#53 (3) {
          ["identifiers"]=>
          array(3) {
            [0]=>
            string(2) "t1"
            [1]=>
            string(2) "id"
            [2]=>
            string(2) "t2"
          }
          ["isOperator"]=>
          bool(false)
          ["expr"]=>
          string(13) "t1.id = t2.id"
        }
      }
      ["using"]=>
      NULL
    }
  }
  ["union"]=>
  array(0) {
  }
  ["end_options"]=>
  NULL
  ["options"]=>
  object(PhpMyAdmin\SqlParser\Components\OptionsArray)#47 (1) {
    ["options"]=>
    array(0) {
    }
  }
  ["first"]=>
  int(0)
  ["last"]=>
  int(39)
}

3、PHP PhpMyAdmin/sql-parser总结