MySQL 语法解析 （三）：SQL文件

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


public class SqlLexer {
    /* generated 2006-12-28.  Refresh occasionally from lexer. */
    static List<String> LEXER = Arrays.asList("ACTION", "ADD", "AFTER", "AGAINST", "AGGREGATE", "ALL", "ALGORITHM", "ALTER", "ANALYZE", "AND", "ANY", "AS", "ASC", "ASCII", "ASENSITIVE", "AUTO_INCREMENT", "AVG", "AVG_ROW_LENGTH", "BACKUP", "BDB", "BEFORE", "BEGIN", "BERKELEYDB", "BETWEEN", "BIGINT", "BINARY", "BINLOG", "BIT", "BLOB", "BOOL", "BOOLEAN", "BOTH", "BTREE", "BY", "BYTE", "CACHE", "CALL", "CASCADE", "CASCADED", "CASE", "CHAIN", "CHANGE", "CHANGED", "CHAR", "CHARACTER", "CHARSET", "CHECK", "CHECKSUM", "CIPHER", "CLIENT", "CLOSE", "CODE", "COLLATE", "COLLATION", "COLUMN", "COLUMNS", "COMMENT", "COMMIT", "COMMITTED", "COMPACT", "COMPRESSED", "CONCURRENT", "CONDITION", "CONNECTION", "CONSISTENT", "CONSTRAINT", "CONTAINS", "CONTINUE", "CONVERT", "CREATE", "CROSS", "CUBE", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "CURRENT_USER", "CURSOR", "DATA", "DATABASE", "DATABASES", "DATE", "DATETIME", "DAY", "DAY_HOUR", "DAY_MICROSECOND", "DAY_MINUTE", "DAY_SECOND", "DEALLOCATE", "DEC", "DECIMAL", "DECLARE", "DEFAULT", "DEFINER", "DELAYED", "DELAY_KEY_WRITE", "DELETE", "DESC", "DESCRIBE", "DES_KEY_FILE", "DETERMINISTIC", "DIRECTORY", "DISABLE", "DISCARD", "DISTINCT", "DISTINCTROW", "DIV", "DO", "DOUBLE", "DROP", "DUAL", "DUMPFILE", "DUPLICATE", "DYNAMIC", "EACH", "ELSE", "ELSEIF", "ENABLE", "ENCLOSED", "END", "ENGINE", "ENGINES", "ENUM", "ERRORS", "ESCAPE", "ESCAPED", "EVENTS", "EXECUTE", "EXISTS", "EXIT", "EXPANSION", "EXPLAIN", "EXTENDED", "FALSE", "FAST", "FETCH", "FIELDS", "FILE", "FIRST", "FIXED", "FLOAT", "FLOAT4", "FLOAT8", "FLUSH", "FOR", "FORCE", "FOREIGN", "FOUND", "FROM", "FULL", "FULLTEXT", "FUNCTION", "GEOMETRY", "GEOMETRYCOLLECTION", "GET_FORMAT", "GLOBAL", "GRANT", "GRANTS", "GROUP", "HANDLER", "HASH", "HAVING", "HELP", "HIGH_PRIORITY", "HOSTS", "HOUR", "HOUR_MICROSECOND", "HOUR_MINUTE", "HOUR_SECOND", "IDENTIFIED", "IF", "IGNORE", "IMPORT", "IN", "INDEX", "INDEXES", "INFILE", "INNER", "INNOBASE", "INNODB", "INOUT", "INSENSITIVE", "INSERT", "INSERT_METHOD", "INT", "INT1", "INT2", "INT3", "INT4", "INT8", "INTEGER", "INTERVAL", "INTO", "IO_THREAD", "IS", "ISOLATION", "ISSUER", "ITERATE", "INVOKER", "JOIN", "KEY", "KEYS", "KILL", "LANGUAGE", "LAST", "LEADING", "LEAVE", "LEAVES", "LEFT", "LEVEL", "LIKE", "LIMIT", "LINES", "LINESTRING", "LOAD", "LOCAL", "LOCALTIME", "LOCALTIMESTAMP", "LOCK", "LOCKS", "LOGS", "LONG", "LONGBLOB", "LONGTEXT", "LOOP", "LOW_PRIORITY", "MASTER", "MASTER_CONNECT_RETRY", "MASTER_HOST", "MASTER_LOG_FILE", "MASTER_LOG_POS", "MASTER_PASSWORD", "MASTER_PORT", "MASTER_SERVER_ID", "MASTER_SSL", "MASTER_SSL_CA", "MASTER_SSL_CAPATH", "MASTER_SSL_CERT", "MASTER_SSL_CIPHER", "MASTER_SSL_KEY", "MASTER_USER", "MATCH", "MAX_CONNECTIONS_PER_HOUR", "MAX_QUERIES_PER_HOUR", "MAX_ROWS", "MAX_UPDATES_PER_HOUR", "MAX_USER_CONNECTIONS", "MEDIUM", "MEDIUMBLOB", "MEDIUMINT", "MEDIUMTEXT", "MERGE", "MICROSECOND", "MIDDLEINT", "MIGRATE", "MINUTE", "MINUTE_MICROSECOND", "MINUTE_SECOND", "MIN_ROWS", "MOD", "MODE", "MODIFIES", "MODIFY", "MONTH", "MULTILINESTRING", "MULTIPOINT", "MULTIPOLYGON", "MUTEX", "NAME", "NAMES", "NATIONAL", "NATURAL", "NDB", "NDBCLUSTER", "NCHAR", "NEW", "NEXT", "NO", "NONE", "NOT", "NO_WRITE_TO_BINLOG", "NULL", "NUMERIC", "NVARCHAR", "OFFSET", "OLD_PASSWORD", "ON", "ONE", "ONE_SHOT", "OPEN", "OPTIMIZE", "OPTION", "OPTIONALLY", "OR", "ORDER", "OUT", "OUTER", "OUTFILE", "PACK_KEYS", "PARTIAL", "PASSWORD", "PHASE", "POINT", "POLYGON", "PRECISION", "PREPARE", "PREV", "PRIMARY", "PRIVILEGES", "PROCEDURE", "PROCESS", "PROCESSLIST", "PURGE", "QUARTER", "QUERY", "QUICK", "READ", "READS", "REAL", "RECOVER", "REDUNDANT", "REFERENCES", "REGEXP", "RELAY_LOG_FILE", "RELAY_LOG_POS", "RELAY_THREAD", "RELEASE", "RELOAD", "RENAME", "REPAIR", "REPEATABLE", "REPLACE", "REPLICATION", "REPEAT", "REQUIRE", "RESET", "RESTORE", "RESTRICT", "RESUME", "RETURN", "RETURNS", "REVOKE", "RIGHT", "RLIKE", "ROLLBACK", "ROLLUP", "ROUTINE", "ROW", "ROWS", "ROW_FORMAT", "RTREE", "SAVEPOINT", "SCHEMA", "SCHEMAS", "SECOND", "SECOND_MICROSECOND", "SECURITY", "SELECT", "SENSITIVE", "SEPARATOR", "SERIAL", "SERIALIZABLE", "SESSION", "SET", "SHARE", "SHOW", "SHUTDOWN", "SIGNED", "SIMPLE", "SLAVE", "SNAPSHOT", "SMALLINT", "SOME", "SONAME", "SOUNDS", "SPATIAL", "SPECIFIC", "SQL", "SQLEXCEPTION", "SQLSTATE", "SQLWARNING", "SQL_BIG_RESULT", "SQL_BUFFER_RESULT", "SQL_CACHE", "SQL_CALC_FOUND_ROWS", "SQL_NO_CACHE", "SQL_SMALL_RESULT", "SQL_THREAD", "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR", "SQL_TSI_DAY", "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR", "SSL", "START", "STARTING", "STATUS", "STOP", "STORAGE", "STRAIGHT_JOIN", "STRING", "STRIPED", "SUBJECT", "SUPER", "SUSPEND", "TABLE", "TABLES", "TABLESPACE", "TEMPORARY", "TEMPTABLE", "TERMINATED", "TEXT", "THEN", "TIME", "TIMESTAMP", "TIMESTAMPADD", "TIMESTAMPDIFF", "TINYBLOB", "TINYINT", "TINYTEXT", "TO", "TRAILING", "TRANSACTION", "TRIGGER", "TRIGGERS", "TRUE", "TRUNCATE", "TYPE", "TYPES", "UNCOMMITTED", "UNDEFINED", "UNDO", "UNICODE", "UNION", "UNIQUE", "UNKNOWN", "UNLOCK", "UNSIGNED", "UNTIL", "UPDATE", "UPGRADE", "USAGE", "USE", "USER", "USER_RESOURCES", "USE_FRM", "USING", "UTC_DATE", "UTC_TIME", "UTC_TIMESTAMP", "VALUE", "VALUES", "VARBINARY", "VARCHAR", "VARCHARACTER", "VARIABLES", "VARYING", "WARNINGS", "WEEK", "WHEN", "WHERE", "WHILE", "VIEW", "WITH", "WORK", "WRITE", "X509", "XOR", "XA", "YEAR", "YEAR_MONTH", "ZEROFILL", "ABS", "ACOS", "ADDDATE", "ADDTIME", "AES_ENCRYPT", "AES_DECRYPT", "AREA", "ASIN", "ASBINARY", "ASTEXT", "ASWKB", "ASWKT", "ATAN", "ATAN2", "BENCHMARK", "BIN", "BIT_COUNT", "BIT_OR", "BIT_AND", "BIT_XOR", "CAST", "CEIL", "CEILING", "BIT_LENGTH", "CENTROID", "CHAR_LENGTH", "CHARACTER_LENGTH", "COALESCE", "COERCIBILITY", "COMPRESS", "CONCAT", "CONCAT_WS", "CONNECTION_ID", "CONV", "CONVERT_TZ", "COUNT", "COS", "COT", "CRC32", "CROSSES", "CURDATE", "CURTIME", "DATE_ADD", "DATEDIFF", "DATE_FORMAT", "DATE_SUB", "DAYNAME", "DAYOFMONTH", "DAYOFWEEK", "DAYOFYEAR", "DECODE", "DEGREES", "DES_ENCRYPT", "DES_DECRYPT", "DIMENSION", "DISJOINT", "ELT", "ENCODE", "ENCRYPT", "ENDPOINT", "ENVELOPE", "EQUALS", "EXTERIORRING", "EXTRACT", "EXP", "EXPORT_SET", "FIELD", "FIND_IN_SET", "FLOOR", "FORMAT", "FOUND_ROWS", "FROM_DAYS", "FROM_UNIXTIME", "GET_LOCK", "GEOMETRYN", "GEOMETRYTYPE", "GEOMCOLLFROMTEXT", "GEOMCOLLFROMWKB", "GEOMETRYCOLLECTIONFROMTEXT", "GEOMETRYCOLLECTIONFROMWKB", "GEOMETRYFROMTEXT", "GEOMETRYFROMWKB", "GEOMFROMTEXT", "GEOMFROMWKB", "GLENGTH", "GREATEST", "GROUP_CONCAT", "GROUP_UNIQUE_USERS", "HEX", "IFNULL", "INET_ATON", "INET_NTOA", "INSTR", "INTERIORRINGN", "INTERSECTS", "ISCLOSED", "ISEMPTY", "ISNULL", "IS_FREE_LOCK", "IS_USED_LOCK", "LAST_INSERT_ID", "ISSIMPLE", "LAST_DAY", "LCASE", "LEAST", "LENGTH", "LN", "LINEFROMTEXT", "LINEFROMWKB", "LINESTRINGFROMTEXT", "LINESTRINGFROMWKB", "LOAD_FILE", "LOCATE", "LOG", "LOG2", "LOG10", "LOWER", "LPAD", "LTRIM", "MAKE_SET", "MAKEDATE", "MAKETIME", "MASTER_POS_WAIT", "MAX", "MBRCONTAINS", "MBRDISJOINT", "MBREQUAL", "MBRINTERSECTS", "MBROVERLAPS", "MBRTOUCHES", "MBRWITHIN", "MD5", "MID", "MIN", "MLINEFROMTEXT", "MLINEFROMWKB", "MPOINTFROMTEXT", "MPOINTFROMWKB", "MPOLYFROMTEXT", "MPOLYFROMWKB", "MONTHNAME", "MULTILINESTRINGFROMTEXT", "MULTILINESTRINGFROMWKB", "MULTIPOINTFROMTEXT", "MULTIPOINTFROMWKB", "MULTIPOLYGONFROMTEXT", "MULTIPOLYGONFROMWKB", "NAME_CONST", "NOW", "NULLIF", "NUMGEOMETRIES", "NUMINTERIORRINGS", "NUMPOINTS", "OCTET_LENGTH", "OCT", "ORD", "OVERLAPS", "PERIOD_ADD", "PERIOD_DIFF", "PI", "POINTFROMTEXT", "POINTFROMWKB", "POINTN", "POLYFROMTEXT", "POLYFROMWKB", "POLYGONFROMTEXT", "POLYGONFROMWKB", "POSITION", "POW", "POWER", "QUOTE", "RADIANS", "RAND", "RELEASE_LOCK", "REVERSE", "ROUND", "ROW_COUNT", "RPAD", "RTRIM", "SEC_TO_TIME", "SESSION_USER", "SUBDATE", "SIGN", "SIN", "SHA", "SHA1", "SLEEP", "SOUNDEX", "SPACE", "SQRT", "SRID", "STARTPOINT", "STD", "STDDEV", "STDDEV_POP", "STDDEV_SAMP", "STR_TO_DATE", "STRCMP", "SUBSTR", "SUBSTRING", "SUBSTRING_INDEX", "SUBTIME", "SUM", "SYSDATE", "SYSTEM_USER", "TAN", "TIME_FORMAT", "TIME_TO_SEC", "TIMEDIFF", "TO_DAYS", "TOUCHES", "TRIM", "UCASE", "UNCOMPRESS", "UNCOMPRESSED_LENGTH", "UNHEX", "UNIQUE_USERS", "UNIX_TIMESTAMP", "UPPER", "UUID", "VARIANCE", "VAR_POP", "VAR_SAMP", "VERSION", "WEEKDAY", "WEEKOFYEAR", "WITHIN", "X", "Y", "YEARWEEK");
    static String DELIMITER_NAME = ";";
    static int DELIMITER_NAME_LEN = 1;


    /**
     * 查询是否是MYSQL命令
     *
     * @param command
     * @return
     */
    public static boolean find_command(String command) {
        String[] commands = command.split(" ");
        if (command.length() >= DELIMITER_NAME_LEN) {
            for (String s : LEXER) {
                if (s.toUpperCase().equals(commands[0])) {
                    return true;
                } else {
                    if (commands[0].length() > 2) {
                        if (s.toUpperCase().equals(commands[0]) || s.toUpperCase().equals(commands[0].substring(0, commands[0].length() - 1))) {
                            return true;
                        }
                    }


                }
            }
            return false;
        }
        return false;
    }


    /**
     * 最后一个位置是否是DELIMITER
     *
     * @param command
     * @return
     */
    public static boolean is_delimiter(String command) {
        if (command.length() >= DELIMITER_NAME_LEN) {
            if (command.substring(command.length() - 1).equals(DELIMITER_NAME)) {
                return true;
            }
        }
        return false;
    }


    /**
     * 分割文件符
     *
     * @param sqltext
     * @return
     */
    public static List<String> parseString(String sqltext) {
        String separator = System.getProperty("line.separator");
        String[] sqltexts = sqltext.split(separator);
        List<String> list = new ArrayList<String>();
        String buffer = "";
        for (String s : sqltexts) {
            if (find_command(s) && !is_delimiter(s)) {
                if (!buffer.isEmpty()) {
                    list.add(buffer);
                    buffer = "";
                }
                buffer = buffer + separator + s;
            } else if (find_command(s) && is_delimiter(s)) {
                if (!buffer.isEmpty()) {
                    list.add(buffer);
                    buffer = "";
                }
                list.add(s);
            } else if (!find_command(s) && !is_delimiter(s)) {
                buffer = buffer + separator + s;
            } else if (!find_command(s) && is_delimiter(s)) {
                buffer = buffer + separator + s;
                list.add(buffer);
                buffer = "";
            }
        }
        return list;
    }
}
复制

import java.io.*;
import java.util.List;


public class Main {
    public static void main(String[] args) {
        String sqls = readToString("/Users/wonmo/Desktop/student.sql");
        System.out.println(sqls);
        List<String> list = SqlLexer.parseString(sqls);
        for (String s : list) {
            System.out.println("=======================================");
            System.out.println(s);
        }
    }
}


复制

=======================================


/*
 Navicat Premium Data Transfer


 Source Server         : mysqled
 Source Server Type    : MySQL
 Source Server Version : 50638
 Source Host           : 192.168.1.71:3306
 Source Schema         : mysql


 Target Server Type    : MySQL
 Target Server Version : 50638
 File Encoding         : 65001


 Date: 15/08/2021 17:02:25
*/


=======================================
SET NAMES utf8mb4;
=======================================
SET FOREIGN_KEY_CHECKS = 0;
=======================================




-- ----------------------------
-- Table structure for student
-- ----------------------------
=======================================
DROP TABLE IF EXISTS `student`;
=======================================


CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
=======================================




-- ----------------------------
-- Records of student
-- ----------------------------
=======================================
BEGIN;
=======================================
INSERT INTO `student` VALUES (1, '男1', 0);
=======================================
INSERT INTO `student` VALUES (2, '男2', 0);
=======================================
INSERT INTO `student` VALUES (3, '男3', 0);
=======================================
INSERT INTO `student` VALUES (4, '女1', 1);
=======================================
INSERT INTO `student` VALUES (5, '女2', 1);
=======================================
COMMIT;
=======================================




=======================================
SET FOREIGN_KEY_CHECKS = 1;
复制