创建表时出错 [英] Error in table creation

问题描述

在MySQL终端上粘贴以下MySQL命令后出现以下错误.这段代码是从项目的生产环境中获取的.我想根据生产环境的数据库更新本地数据库.

I got the below error after pasting the below MySQL command on MySQL terminal.This code I got it from my project's production environment.I want to update the local db according to the production env's db.

mysql> CREATE TABLE `cityguide_pointofinterest` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(30) COLLATE utf8_danish_ci NOT NULL,
    ->   `vip` tinyint(1) NOT NULL,
    ->   `description` longtext COLLATE utf8_danish_ci NOT NULL,
    ->   `category_id` int(11) NOT NULL,
    ->   `email` varchar(254) COLLATE utf8_danish_ci,
    ->   `latitude` double NOT NULL,
    ->   `longitude` double NOT NULL,
    ->   `phone` varchar(15) COLLATE utf8_danish_ci,
    ->   `place` varchar(250) COLLATE utf8_danish_ci DEFAULT NULL,
    ->   `website` varchar(200) COLLATE utf8_danish_ci,
    ->   `date_created` datetime(6) NOT NULL,
    ->   `date_modified` datetime(6) NOT NULL,
    ->   `views` int(11) NOT NULL,
    ->   `picture_1` varchar(100) COLLATE utf8_danish_ci,
    ->   `picture_2` varchar(100) COLLATE utf8_danish_ci,
    ->   `picture_3` varchar(100) COLLATE utf8_danish_ci,
    ->   `order_id` varchar(20) COLLATE utf8_danish_ci DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `cityguide_pointofi_category_id_8ab78aba_fk_cityguide_category_id` (`category_id`),
    ->   CONSTRAINT `cityguide_pointofi_category_id_8ab78aba_fk_cityguide_category_id` FOREIGN KEY (`category_id`) REFERENCES `cityguide_category` (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;

错误1064(42000):您的SQL语法有错误；查看与您的MySQL服务器版本相对应的手册，以获取正确的语法，以在'(6)NOT NULL附近使用， date_modified datetime(6)NOT NULL， 第13行的views int(11)NOT NULL' mysql>/*！40101 SET character_set_client = @saved_cs_client */; 查询正常，受影响的0行(0.00秒)

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, date_modified datetime(6) NOT NULL, views int(11) NOT NULL' at line 13 mysql> /*!40101 SET character_set_client = @saved_cs_client */; Query OK, 0 rows affected (0.00 sec)

推荐答案

我认为您的MYSQL版本为5.5或更低.

Your MYSQL version is 5.5 or below i think.

MySQL 5.7 支持TIME，DATETIME和TIMESTAMP值的小数秒支持，精度最高为微秒(6位数):

MySQL 5.7 has fractional seconds support for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:

要定义包含小数秒部分的列，请使用 语法type_name(fsp)，其中type_name是TIME，DATETIME或 TIMESTAMP，fsp是小数秒精度.例如:

To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

fsp值(如果给出)必须在0到6的范围内.值0表示没有小数部分.如果省略，则默认精度为0.(这与标准SQL默认值6不同，以便与以前的MySQL版本兼容.)

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

SQL FIDDLE DEMO

SQL FIDDLE DEMO

这篇关于创建表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！