错误1426(42000)在第20行:太大的精度14。

I have been given an sql file and am trying to import it with MySQL command line. When I type the following

我得到了一个sql文件，并试图用MySQL命令行导入它。当我键入下面的内容时。

C:\Users\MyUser\Desktop\SQL>mysql -u root -p < memory.sql
Enter password: **********

I get this error

我得到这个错误

ERROR 1426 (42000) at line 20: Too big precision 14 specified for column 'lastmod'. Maximum is 6

SO I opened the sql file in a text editor, changed 14 to 6, try to import again and I get this:

所以我在一个文本编辑器中打开了sql文件，改变了14到6，再次尝试导入，我得到了这个:

ERROR 1064 (42000) at line 20: 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 'TYPE=MyISAM' at line 16

Here are the create tables

这里是创建表。

CREATE TABLE AccessCard (
 ID int(11) NOT NULL auto_increment,
 personID int(11) NOT NULL default '0',
 cardFormatID int(11) NOT NULL default '0',
 cardNumber char(6) binary default NULL,
 friendNumber bigint(20) default NULL,
 disabled tinyint(1) NOT NULL default '0',
 issueCode int(11) default NULL,
 lastmod timestamp(14) NOT NULL,
 lastmodPersonID int(11) default NULL,
 PRIMARY KEY  (ID),
 UNIQUE KEY Alter_Key1 (cardFormatID,cardNumber),
 KEY AI_ID (ID),
 KEY IX_cardFormatToAccessCard (cardFormatID),
 KEY IX_PersonToAccessCard (personID)
 ) TYPE=MyISAM;

I have googled this and the only thing I found landed me here, but it seemed to be from someone that was creating a new database. I'm fairly new to MySQL, so be gentle. Thanks!

我在谷歌上搜索了一下，我发现的唯一一件事就是在这里找到了我，但似乎是有人在创建一个新的数据库。我对MySQL很陌生，所以要温柔。谢谢!

1 个解决方案

#1

This works in MySQL 5.5.32.

这在MySQL 5.5.32中有效。

CREATE TABLE AccessCard (
 ID int(11) NOT NULL auto_increment,
 personID int(11) NOT NULL default '0',
 cardFormatID int(11) NOT NULL default '0',
 cardNumber char(6) binary default NULL,
 friendNumber bigint(20) default NULL,
 disabled tinyint(1) NOT NULL default '0',
 issueCode int(11) default NULL,
 lastmod timestamp NOT NULL,
 lastmodPersonID int(11) default NULL,
 PRIMARY KEY  (ID),
 UNIQUE KEY Alter_Key1 (cardFormatID,cardNumber),
 KEY AI_ID (ID),
 KEY IX_cardFormatToAccessCard (cardFormatID),
 KEY IX_PersonToAccessCard (personID)
 ) ENGINE=MyISAM;

There are two changes.

有两个变化。

Drop the "(14)" from the line lastmod timestamp NOT NULL,.
将“(14)”从lastmod时间戳中删除，而不是NULL。
Change type=MyISAM to ENGINE=MyISAM. (This might not be necessary on your server. I'm not sure whether this is actually a syntax problem or simply a difference in server configuration.)
改变类型= MyISAM = MyISAM引擎。(这在您的服务器上可能不是必需的。我不确定这到底是语法问题还是服务器配置的不同。

There could be a version mismatch or server configuration mismatch here. My version doesn't dump timestamp columns as timestamp(14). My version dumps them as simply timestamp.

这里可能存在版本不匹配或服务器配置不匹配。我的版本不把时间戳列作为时间戳(14)。我的版本将它们转储为简单的时间戳。

#1