A-A+

SQL解析工具tmysqlparse使用说明(附工具)

2015年06月18日 sqlparse, TenDB 评论 4 条

SQL解析工具tmysqlparse使用说明(附工具)

在dtcc 2015,腾讯互动娱运营部DBA团队做了“SQL解析工具在腾讯游戏GCS平台的应用”的分享,详情见http://tencentdba.com/blog/sqlparse_dtcc2015/。本文会将该工具分享出来,并提供工具的使用说明。

SQL解析工具tmysqlparse的git地址:https://github.com/GCSAdmin/tmysqlparse,里面是tmysqlparse_v2.0为32位linux版本的打包工具,而tmysqlparse_x64则为64位linux版本的工具。

在文章http://tencentdba.com/blog/sqlparse/ 对tmysqlparse工具有了初步的介绍,下面详细介绍tmysqlparse工具如何使用。

下载工具后,解压会有如下内容,如图1.

{X~@_8_H$HQ_VFV5NZA4OH4

图 1 sqlparse工具内容

tmysqlparse,即可执行工具;

libtmysqlparse.so.18.0.0,依赖包;

share,目录中包含语法错误后的错误信息;

tmysqlparse.sh,设置环境变量及相对目录,通过该脚本来执行工具。

通过--help选项可以查看工具使用说明,如图2。

ECLC8B%8N}9`UR{%1WR@]17

图2 tmysqlparse选项说明

tmysqlparse工具主要用于语法检查及提示高危告警,可以通过

./tmysqlparse.sh -c latin1 -v tmysql-1.4 test的方式在console中直接输入sql语句,在\q退出console后会显示语法检查及告警信息。

实际中一般会以./tmysqlparse.sh -c latin1 -v tmysql-1.4 -f check.result test < to_check.sql的方式来使用。to_check.sql为待检查的SQL语句,检查结果则以xml的方式输出的文件check.result中。应用就可以解析xml来获取相关信息,图3对xml中的标签进行说明。

QQ图片20150623131246

图3 语法检查xml标签说明

上述使用tmysqlparse的方法,可以分析所mysql所有的语法错误,但其中的高危告警却是据腾讯游戏的应用场景制定,可能在其它场合并不适用。另外,一些应用场景需要对sql语句进行解析,提取sql语句类型,涉及库、表、索引等信息。为此tmysqlparse提供了下面这种使用方法。

./tmysqlparse.sh -c latin1 -v tmysql-1.4 -w -W output.xml test < a.sql

其中a.sql中的SQL语句如图4

56`QCRM{UOIA~AHYDPV%WO9

图4 a.sql内容

通过-w -W选项,将a.sql中的SQL语句进行解析,解析结果输出到文件output.xml中。图5对output.xml进行具体说明。

QQ图片20150623131302

图5 SQL分析xml说明

通过-w -W指定输出xml的方式可以得到SQL语句类型,主要类型有如下: ----------------------------------------------------------------------

switch(type)

{

case SQLCOM_SELECT: return "STMT_SELECT";

case SQLCOM_CREATE_TABLE: return "STMT_CREATE_TABLE";

case SQLCOM_CREATE_INDEX: return "STMT_CREATE_INDEX";

case SQLCOM_ALTER_TABLE: return "STMT_ALTER_TABLE";

case SQLCOM_UPDATE: return "STMT_UPDATE";

case SQLCOM_INSERT: return "STMT_INSERT";

case SQLCOM_INSERT_SELECT: return "STMT_INSERT_SELECT";

case SQLCOM_DELETE: return "STMT_DELETE";

case SQLCOM_TRUNCATE: return "STMT_TRUNCATE";

case SQLCOM_DROP_TABLE: return "STMT_DROP_TABLE";

case SQLCOM_DROP_INDEX: return "STMT_DROP_INDEX";

case SQLCOM_SHOW_DATABASES: return "STMT_SHOW_DATABASES";

case SQLCOM_SHOW_TABLES: return "STMT_SHOW_TABLES";

case SQLCOM_SHOW_FIELDS: return "STMT_SHOW_FIELDS";

case SQLCOM_SHOW_KEYS: return "STMT_SHOW_KEYS";

case SQLCOM_SHOW_VARIABLES: return "STMT_SHOW_VARIABLES";

case SQLCOM_SHOW_STATUS: return "STMT_SHOW_STATUS";

case SQLCOM_SHOW_ENGINE_LOGS: return "STMT_SHOW_ENGINE_LOGS";

case SQLCOM_SHOW_ENGINE_STATUS: return "STMT_SHOW_ENGINE_STATUS";

case SQLCOM_SHOW_ENGINE_MUTEX: return "STMT_SHOW_ENGINE_MUTEX";

case SQLCOM_SHOW_PROCESSLIST: return "STMT_SHOW_PROCESSLIST";

case SQLCOM_SHOW_MASTER_STAT: return "STMT_SHOW_MASTER_STAT";

case SQLCOM_SHOW_SLAVE_STAT: return "STMT_SHOW_SLAVE_STAT";

case SQLCOM_SHOW_GRANTS: return "STMT_SHOW_GRANTS";

case SQLCOM_SHOW_CREATE: return "STMT_SHOW_CREATE";

case SQLCOM_SHOW_CHARSETS: return "STMT_SHOW_CHARSETS";

case SQLCOM_SHOW_COLLATIONS: return "STMT_SHOW_COLLATIONS";

case SQLCOM_SHOW_CREATE_DB: return "STMT_SHOW_CREATE_DB";

case SQLCOM_SHOW_TABLE_STATUS: return "STMT_SHOW_TABLE_STATUS";

case SQLCOM_SHOW_TRIGGERS: return "STMT_SHOW_TRIGGERS";

case SQLCOM_LOAD: return "STMT_LOAD";

case SQLCOM_SET_OPTION: return "STMT_SET_OPTION";

case SQLCOM_LOCK_TABLES: return "STMT_LOCK_TABLES";

case SQLCOM_UNLOCK_TABLES: return "STMT_UNLOCK_TABLES";

case SQLCOM_GRANT: return "STMT_GRANT";

case SQLCOM_CHANGE_DB: return "STMT_CHANGE_DB";

case SQLCOM_CREATE_DB: return "STMT_CREATE_DB";

case SQLCOM_DROP_DB: return "STMT_DROP_DB";

case SQLCOM_ALTER_DB: return "STMT_ALTER_DB";

case SQLCOM_REPAIR: return "STMT_REPAIR";

case SQLCOM_REPLACE: return "STMT_REPLACE";

case SQLCOM_REPLACE_SELECT: return "STMT_REPLACE_SELECT";

case SQLCOM_CREATE_FUNCTION: return "STMT_CREATE_FUNCTION";

case SQLCOM_DROP_FUNCTION: return "STMT_DROP_FUNCTION";

case SQLCOM_REVOKE: return "STMT_REVOKE";

case SQLCOM_OPTIMIZE: return "STMT_OPTIMIZE";

case SQLCOM_CHECK: return "STMT_CHECK";

case SQLCOM_ASSIGN_TO_KEYCACHE: return "STMT_ASSIGN_TO_KEYCACHE";

case SQLCOM_PRELOAD_KEYS: return "STMT_PRELOAD_KEYS";

case SQLCOM_FLUSH: return "STMT_FLUSH";

case SQLCOM_KILL: return "STMT_KILL";

case SQLCOM_ANALYZE: return "STMT_ANALYZE";

case SQLCOM_ROLLBACK: return "STMT_ROLLBACK";

case SQLCOM_ROLLBACK_TO_SAVEPOINT: return "STMT_ROLLBACK_TO_SAVEPOINT";

case SQLCOM_COMMIT: return "STMT_COMMIT";

case SQLCOM_SAVEPOINT: return "STMT_SAVEPOINT";

case SQLCOM_RELEASE_SAVEPOINT: return "STMT_RELEASE_SAVEPOINT";

case SQLCOM_SLAVE_START: return "STMT_SLAVE_START";

case SQLCOM_SLAVE_STOP: return "STMT_SLAVE_STOP";

case SQLCOM_BEGIN: return "STMT_BEGIN";

case SQLCOM_CHANGE_MASTER: return "STMT_CHANGE_MASTER";

case SQLCOM_RENAME_TABLE: return "STMT_RENAME_TABLE";

case SQLCOM_RESET: return "STMT_RESET";

case SQLCOM_PURGE: return "STMT_PURGE";

case SQLCOM_PURGE_BEFORE: return "STMT_PURGE_BEFORE";

case SQLCOM_SHOW_BINLOGS: return "STMT_SHOW_BINLOGS";

case SQLCOM_SHOW_OPEN_TABLES: return "STMT_SHOW_OPEN_TABLES";

case SQLCOM_HA_OPEN: return "STMT_HA_OPEN";

case SQLCOM_HA_CLOSE: return "STMT_HA_CLOSE";

case SQLCOM_HA_READ: return "STMT_HA_READ";

case SQLCOM_SHOW_SLAVE_HOSTS: return "STMT_SHOW_SLAVE_HOSTS";

case SQLCOM_DELETE_MULTI: return "STMT_DELETE_MULTI";

case SQLCOM_UPDATE_MULTI: return "STMT_UPDATE_MULTI";

case SQLCOM_SHOW_BINLOG_EVENTS: return "STMT_SHOW_BINLOG_EVENTS";

case SQLCOM_DO: return "STMT_DO";

case SQLCOM_SHOW_WARNS: return "STMT_SHOW_WARNS";

case SQLCOM_EMPTY_QUERY: return "STMT_EMPTY_QUERY";

case SQLCOM_SHOW_ERRORS: return "STMT_SHOW_ERRORS";

case SQLCOM_SHOW_STORAGE_ENGINES: return "STMT_SHOW_STORAGE_ENGINES";

case SQLCOM_SHOW_PRIVILEGES: return "STMT_SHOW_PRIVILEGES";

case SQLCOM_HELP: return "STMT_HELP";

case SQLCOM_CREATE_USER: return "STMT_CREATE_USER";

case SQLCOM_DROP_USER: return "STMT_DROP_USER";

case SQLCOM_RENAME_USER: return "STMT_RENAME_USER";

case SQLCOM_REVOKE_ALL: return "STMT_REVOKE_ALL";

case SQLCOM_CHECKSUM: return "STMT_CHECKSUM";

case SQLCOM_CREATE_PROCEDURE: return "STMT_CREATE_PROCEDURE";

case SQLCOM_CREATE_SPFUNCTION: return "STMT_CREATE_SPFUNCTION";

case SQLCOM_CALL: return "STMT_CALL";

case SQLCOM_DROP_PROCEDURE: return "STMT_DROP_PROCEDURE";

case SQLCOM_ALTER_PROCEDURE: return "STMT_ALTER_PROCEDURE";

case SQLCOM_ALTER_FUNCTION: return "STMT_ALTER_FUNCTION";

case SQLCOM_SHOW_CREATE_PROC: return "STMT_SHOW_CREATE_PROC";

case SQLCOM_SHOW_CREATE_FUNC: return "STMT_SHOW_CREATE_FUNC";

case SQLCOM_SHOW_STATUS_PROC: return "STMT_SHOW_STATUS_PROC";

case SQLCOM_SHOW_STATUS_FUNC: return "STMT_SHOW_STATUS_FUNC";

case SQLCOM_PREPARE: return "STMT_PREPARE";

case SQLCOM_EXECUTE: return "STMT_EXECUTE";

case SQLCOM_DEALLOCATE_PREPARE: return "STMT_DEALLOCATE_PREPARE";

case SQLCOM_CREATE_VIEW: return "STMT_CREATE_VIEW";

case SQLCOM_DROP_VIEW: return "STMT_DROP_VIEW";

case SQLCOM_CREATE_TRIGGER: return "STMT_CREATE_TRIGGER";

case SQLCOM_DROP_TRIGGER: return "STMT_DROP_TRIGGER";

case SQLCOM_XA_START: return "STMT_XA_START";

case SQLCOM_XA_END: return "STMT_XA_END";

case SQLCOM_XA_PREPARE: return "STMT_XA_PREPARE";

case SQLCOM_XA_COMMIT: return "STMT_XA_COMMIT";

case SQLCOM_XA_ROLLBACK: return "STMT_XA_ROLLBACK";

case SQLCOM_XA_RECOVER: return "STMT_XA_RECOVER";

case SQLCOM_SHOW_PROC_CODE: return "STMT_SHOW_PROC_CODE";

case SQLCOM_SHOW_FUNC_CODE: return "STMT_SHOW_FUNC_CODE";

case SQLCOM_ALTER_TABLESPACE: return "STMT_ALTER_TABLESPACE";

case SQLCOM_INSTALL_PLUGIN: return "STMT_INSTALL_PLUGIN";

case SQLCOM_UNINSTALL_PLUGIN: return "STMT_UNINSTALL_PLUGIN";

case SQLCOM_SHOW_AUTHORS: return "STMT_SHOW_AUTHORS";

case SQLCOM_BINLOG_BASE64_EVENT: return "STMT_BINLOG_BASE64_EVENT";

case SQLCOM_SHOW_PLUGINS: return "STMT_SHOW_PLUGINS";

case SQLCOM_SHOW_CONTRIBUTORS: return "STMT_SHOW_CONTRIBUTORS";

case SQLCOM_CREATE_SERVER: return "STMT_CREATE_SERVER";

case SQLCOM_DROP_SERVER: return "STMT_DROP_SERVER";

case SQLCOM_ALTER_SERVER: return "STMT_ALTER_SERVER";

case SQLCOM_CREATE_EVENT: return "STMT_CREATE_EVENT";

case SQLCOM_ALTER_EVENT: return "STMT_ALTER_EVENT";

case SQLCOM_DROP_EVENT: return "STMT_DROP_EVENT";

case SQLCOM_SHOW_CREATE_EVENT: return "STMT_SHOW_CREATE_EVENT";

case SQLCOM_SHOW_EVENTS: return "STMT_SHOW_EVENTS";

case SQLCOM_SHOW_CREATE_TRIGGER: return "STMT_SHOW_CREATE_TRIGGER";

case SQLCOM_ALTER_DB_UPGRADE: return "STMT_ALTER_DB_UPGRADE";

case SQLCOM_SHOW_PROFILE: return "STMT_SHOW_PROFILE";

case SQLCOM_SHOW_PROFILES: return "STMT_SHOW_PROFILES";

case SQLCOM_SIGNAL: return "STMT_SIGNAL";

case SQLCOM_RESIGNAL: return "STMT_RESIGNAL";

case SQLCOM_SHOW_RELAYLOG_EVENTS: return "STMT_SHOW_RELAYLOG_EVENTS";

------------------------------------------------------------

除上述类型外,额外增加了几种自定义类型:

STMT_CREATE_TABLE_LIKE, 表示create table like语句

STMT_CREATE_TABLE_SELECT,表示create table select语句

STMT_CREATE_TABLE_WITH_CONNECT_STRING,表示create table时指定了connect

STMT_CREATE_TABLE_WITH_FIELD_CHARSET,表示create table时列上指定了字符集

原创文章,转载请注明: 转载自腾讯游戏DBA团队

本文链接地址: SQL解析工具tmysqlparse使用说明(附工具)

文章的脚注信息由WordPress的wp-posturl插件自动生成

  1. 如果tencent game dba团队之外,谁用了的话,希望能给我们多一点点反馈,i’m robincui

  2. 你好,5.5的很不错,但是有没有5.6版本的支持,最近升到5.6了,多个timestamp的默认值的应用也越来越多,导致旧版本的审核会影响某些语句的正常提交

Copyright © 腾讯游戏DBA团队 保留所有权利.  

用户登录

分享到: