为什么这个SQL在MySql的一个版本上工作，而不是在另一个版本上工作呢？

Question

我试图在两个不同的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失败了。但是，当我对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)

Answer 1

数据类型DATETIME过去仅为yyyy-mm-dd hh:mm:ss。但是，从5.6.4(?)开始，允许使用分数秒。DATETIME(6)允许微秒：yyyy-mm-dd hh:mm:ss.ffffff。

如果希望该CREATE在这两个版本上都能工作，请删除(6)。注意错误消息是如何准确地指向这一点的。