為什麼這個 SQL 可以在一個版本的 MySql-Server 上工作，而不能在另一個版本上工作？

我正在嘗試在兩個不同的 MySQL 數據庫上執行以下非常簡單的 SQL 命令：

CREATE TABLE mytable (
   created DATETIME(6) NOT NULL, 
   updated DATETIME(6) NOT NULL, 
   my_id INTEGER NOT NULL AUTO_INCREMENT, 
   name VARCHAR(255) NOT NULL, 
   PRIMARY KEY (my_id), 
   UNIQUE (name)
);

當我針對mysql-server 5.5. 但是當我執行它時它成功了mysql-server 5.6。為什麼？

下面你可以看到它是如何實際執行的。顯然 5.5 不允許使用 DATETIME 列給出整數大小。

失敗案例：

   # mysql -D my_db_instance -h 127.0.0.1
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 938
   Server version: 5.5.46 MySQL Community Server (GPL)

   Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> CREATE TABLE mytable (
       ->     created DATETIME(6) NOT NULL,
       ->     updated DATETIME(6) NOT NULL,
       ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
       ->     name VARCHAR(255) NOT NULL,
       ->     PRIMARY KEY (my_id),
       ->     UNIQUE (name)
       -> );

   ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(6) NOT NULL,
       updated DATETIME(6) NOT NULL,
       my_id INTEGER NOT NULL A' at line 2

成功案例：

   $ mysql -u myuser -p -D my_db_instance -h 127.0.0.1
   Enter password:
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A

   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 6
   Server version: 5.6.21 Homebrew

   Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.

   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   mysql> CREATE TABLE mytable (
       ->     created DATETIME(6) NOT NULL,
       ->     updated DATETIME(6) NOT NULL,
       ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
       ->     name VARCHAR(255) NOT NULL,
       ->     PRIMARY KEY (my_id),
       ->     UNIQUE (name)
       -> );
   Query OK, 0 rows affected (0.01 sec)

數據類型DATETIME曾經是 only yyyy-mm-dd hh:mm:ss。但是，從 5.6.4(?) 開始，允許使用 小數秒。DATETIME(6)允許微秒： yyyy-mm-dd hh:mm:ss.ffffff.

如果您希望它CREATE在兩個版本上都有效，請刪除(6). 請注意錯誤消息是如何準確指出的。

引用自：https://dba.stackexchange.com/questions/134156