创建mysql表报错

[J1031]

测试代码如下

@Test
    public void createTable() throws Exception {
        executeUpdate("drop database if exists mysql_db2");
        executeUpdate("create database if not exists mysql_db2 "
                + "default character set utf8mb4 collate = utf8mb4_general_ci ");

        executeUpdate("CREATE TABLE `t1` (\n" +
                "  `id` bigint(20) NOT NULL AUTO_INCREMENT,\n" +
                "  `f1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'f1',\n" +
                "  `f2` smallint(6) DEFAULT NULL COMMENT 'f2',\n" +
                "  `f3` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',\n" +
                "  `f4` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',\n" +
                "  PRIMARY KEY (`id`) USING BTREE\n" +
                ") ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='t1';\n");
    }

服务端抛的异常

ERROR 08:44:04.000 Failed to prepare statement: CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `f1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'f1',
  `f2` smallint(6) DEFAULT NULL COMMENT 'f2',
  `f3` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',
  `f4` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='t1';
org.lealone.common.exceptions.JdbcSQLException: Syntax error in SQL statement "CREATE TABLE ""T1"" (
  ""ID"" BIGINT(20) NOT NULL AUTO_INCREMENT,
  ""F1"" VARCHAR(100) CHARACTER[*] SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL COMMENT 'f1',
  ""F2"" SMALLINT(6) DEFAULT NULL COMMENT 'f2',
  ""F3"" DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',
  ""F4"" DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',
  PRIMARY KEY (""ID"") USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=84 DEFAULT CHARSET=UTF8MB4 ROW_FORMAT=DYNAMIC COMMENT='t1'; "; expected "FOR, BINARY, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )"; SQL statement:
CREATE TABLE `t1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `f1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'f1',
  `f2` smallint(6) DEFAULT NULL COMMENT 'f2',
  `f3` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',
  `f4` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='t1'; [42001-0]
	at org.lealone.common.exceptions.DbException.getJdbcSQLException(DbException.java:342) ~[classes/:?]
	at org.lealone.common.exceptions.DbException.getSyntaxError(DbException.java:198) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.getSyntaxError(SQLParserBase.java:550) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.read(SQLParserBase.java:3034) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.readIfMore(SQLParserBase.java:906) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parseTableDefinition(SQLParserBase.java:5859) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parseCreateTable(SQLParserBase.java:5883) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parseCreate(SQLParserBase.java:4142) ~[classes/:?]
	at org.lealone.plugins.mysql.sql.MySQLParser.parseCreate(MySQLParser.java:61) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parseStatement(SQLParserBase.java:368) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:323) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:285) ~[classes/:?]
	at org.lealone.sql.SQLParserBase.parse(SQLParserBase.java:182) ~[classes/:?]
	at org.lealone.db.session.ServerSession.prepareStatement(ServerSession.java:475) ~[classes/:?]
	at org.lealone.plugins.mysql.server.MySQLServerConnection.prepareStatement(MySQLServerConnection.java:186) [classes/:?]
	at org.lealone.plugins.mysql.server.MySQLServerConnection.executeStatement(MySQLServerConnection.java:206) [classes/:?]
	at org.lealone.plugins.mysql.server.handler.CommandPacketHandler.handle(CommandPacketHandler.java:37) [classes/:?]
	at org.lealone.plugins.mysql.server.MySQLServerConnection.handle(MySQLServerConnection.java:389) [classes/:?]
	at org.lealone.net.nio.NioEventLoop.read(NioEventLoop.java:258) [classes/:?]
	at org.lealone.net.nio.NioEventLoop.handleSelectedKeys(NioEventLoop.java:475) [classes/:?]
	at org.lealone.server.Scheduler.runEventLoop(Scheduler.java:504) [classes/:?]
	at org.lealone.server.Scheduler.run(Scheduler.java:135) [classes/:?]

客户端抛的异常

java.lang.RuntimeException: java.sql.SQLException: Syntax error in SQL statement "CREATE TABLE ""T1"" (
  ""ID"" BIGINT(20) NOT NULL AUTO_INCREMENT,
  ""F1"" VARCHAR(100) CHARACTER[*] SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL COMMENT 'f1',
  ""F2"" SMALLINT(6) DEFAULT NULL COMMENT 'f2',
  ""F3"" DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',
  ""F4"" DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',
  PRIMARY KEY (""ID"") USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=84 DEFAULT CHARSET=UTF8MB4 ROW_FORMAT=DYNAMIC COMMENT='t1'; "; expected "FOR, BINARY, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )" [42001-0]

	at org.lealone.test.sql.SqlTestBase.executeUpdate(SqlTestBase.java:154)
	at org.lealone.test.plugins.mysql.MySQLSyntaxTest.createTable(MySQLSyntaxTest.java:44)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: java.sql.SQLException: Syntax error in SQL statement "CREATE TABLE ""T1"" (
  ""ID"" BIGINT(20) NOT NULL AUTO_INCREMENT,
  ""F1"" VARCHAR(100) CHARACTER[*] SET UTF8MB4 COLLATE UTF8MB4_UNICODE_CI DEFAULT NULL COMMENT 'f1',
  ""F2"" SMALLINT(6) DEFAULT NULL COMMENT 'f2',
  ""F3"" DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'f3',
  ""F4"" DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'f4',
  PRIMARY KEY (""ID"") USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=84 DEFAULT CHARSET=UTF8MB4 ROW_FORMAT=DYNAMIC COMMENT='t1'; "; expected "FOR, BINARY, UNSIGNED, NOT, NULL, AS, DEFAULT, GENERATED, NOT, NULL, AUTO_INCREMENT, BIGSERIAL, SERIAL, IDENTITY, NULL_TO_DEFAULT, SEQUENCE, SELECTIVITY, COMMENT, CONSTRAINT, PRIMARY, UNIQUE, NOT, NULL, CHECK, REFERENCES, ,, )" [42001-0]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1335)
	at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
	at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1246)
	at org.lealone.test.sql.SqlTestBase.executeUpdate(SqlTestBase.java:150)
	... 30 more

[codefollower]

mysql 的一些 sql 语句细节还没有全部实现，最近的工作是支持 enum 类型，下一步会把 dml/ddl 语句逐条过一遍，查缺补漏，把一些语法细节都解析了，能实现的就实现，不需要实现的解析完语法后就直接忽略，这样就不会报告语法错误了。