MySQL 带有 TINYTEXT 列的 CREATE TABLE 语句中的语法错误 1064？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11218713/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Syntax error 1064 in CREATE TABLE statement with TINYTEXT columns?
提问by doodlesalot
This is the MySQL code I have so far:
这是我到目前为止的 MySQL 代码：
CREATE DATABASE bankbase;
USE bankbase;
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL, ClientEmail
TINYTEXT(30) NULL,
ClientAddress TINYTEXT(128) NOT NULL,
PRIMARYKEY(ClientID)
);
CREATE TABLE branchtable(
BranchID SMALLINT(15) NOT NULL DEFAULT 0,
BranchCity TINYTEXT(30) NOT NULL DEFAULT city,
BranchManagerFName VARCHAR(30) NULL DEFAULT "Branch Manager's First Name",
BranchManagerLName VARCHAR(30) NULL
DEFAULT "Branch Manager's LAst Name",
BranchPhone CHAR(10) NOT NULL,
BranchEmail TINYTEXT(30) NULL DEFAULT @bank.com,
PRIMARYKEY(BranchID)
);
CREATE TABLE transactiontable(
TypeID SMALLINT(15) NOT NULL DEFAULT 0,
Type ENUM('CHEQUING','SAVINGS') NOT NULL,
TransAmount DECIMAL NOT NULL,
TransDate TIMESTAMP NOT NULL,
Balance DOUBLE NOT NULL,
PRIMARYKEY(TypeID)
);
Running this code in MySQL on the command line gives me this error:
在命令行中在 MySQL 中运行此代码给我这个错误：
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 '(TypeID) )' at line 7
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 7 行的“(TypeID))”附近使用的正确语法
I've tried creating different test tables, leaving out the NULL/NOT NULL, but nothing seems to work. Any help would be very, very appreciated!
我尝试创建不同的测试表，忽略 NULL/NOT NULL，但似乎没有任何效果。任何帮助将非常非常感谢！
回答by Ricardo Altamirano
Here are the problems I see with your original script:
以下是我在您的原始脚本中看到的问题：
BLOBandTEXTcolumns cannot haveDEFAULTvalues.TINYTEXTis the same asVARCHAR(255), so you can't declare a maximum length for aTINYTEXTfield because one is already implied.You need a space between the words
PRIMARYKEY. It should bePRIMARY KEY.Finally, it isn't a problem per se, but in your first
CREATE TABLEstatement, you have odd spacing. Changing theClientEmailline to the following makes it a lot more readable:
BLOB并且TEXT列不能有DEFAULT值。TINYTEXT与 相同VARCHAR(255)，因此您不能为TINYTEXT字段声明最大长度，因为已经隐含了一个。单词之间需要一个空格
PRIMARYKEY。应该是PRIMARY KEY。最后，这本身不是问题，但在您的第一条
CREATE TABLE语句中，您有奇数间距。将该ClientEmail行更改为以下内容使其更具可读性：
Much better:
好多了：
ClientPhone CHAR(10) NOT NULL,
ClientEmail TINYTEXT NOT NULL,
See the MySQL documentationfor more information. After all of those corrections, these are the working MySQL queries:
有关更多信息，请参阅MySQL 文档。在所有这些更正之后，这些是有效的 MySQL 查询：
CREATE DATABASE bankbase;
USE bankbase;
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL,
ClientEmail TINYTEXT NULL,
ClientAddress TINYTEXT NOT NULL,
PRIMARY KEY(ClientID)
);
CREATE TABLE branchtable(
BranchID SMALLINT(15) NOT NULL DEFAULT 0,
BranchCity TINYTEXT NOT NULL,
BranchManagerFName VARCHAR(30) NULL DEFAULT "Branch Manager's First Name",
BranchManagerLName VARCHAR(30) NULL DEFAULT "Branch Manager's LAst Name",
BranchPhone CHAR(10) NOT NULL,
BranchEmail TINYTEXT NULL,
PRIMARY KEY(BranchID)
);
CREATE TABLE transactiontable(
TypeID SMALLINT(15) NOT NULL DEFAULT 0,
Type ENUM('CHEQUING','SAVINGS') NOT NULL,
TransAmount DECIMAL NOT NULL,
TransDate TIMESTAMP NOT NULL,
Balance DOUBLE NOT NULL,
PRIMARY KEY(TypeID)
);
If you click Build Schemain this SQL fiddle, you'll see that it works!
如果你点击Build Schema在这个SQL小提琴，你会看到，它的作品！
A bit more information on how to methodically fix errors like this
有关如何有条不紊地修复此类错误的更多信息
If you're methodical, these problems are easy to solve, especially with CREATE TABLEstatements. For example, when you're debugging the first CREATE TABLEstatement, move through the column declarations one at at time.
如果您有条不紊，这些问题很容易解决，尤其是使用CREATE TABLE语句。例如，在调试第一条CREATE TABLE语句时，一次一个地浏览列声明。
Try making a table with just the first column:
尝试制作一个只有第一列的表格：
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0);
That code works so delete the table and add columns one by one until you add one that throws an error:
该代码有效，因此删除表并一一添加列，直到添加引发错误的列：
DROP TABLE clienttable;
CREATE TABLE clienttable(
ClientID SMALLINT(15) NOT NULL DEFAULT 0,
ClientFirstName VARCHAR(30) NOT NULL DEFAULT "first name",
ClientLastName VARCHAR(30) NOT NULL DEFAULT "last name",
ClientPhone CHAR(10) NOT NULL, ClientEmail
TINYTEXT(30) NULL);
We get the error you asked about:
我们收到您询问的错误：
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 '(30) NULL)' at line 7
ERROR 1064 (42000)：您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 7 行的“(30) NULL)”附近使用的正确语法
Now you know exactly which line has the error. I've even seen code written like this occasionally (this is the same code as in the previous example):
现在您确切地知道哪一行有错误。我什至偶尔会看到这样写的代码（这与上一个示例中的代码相同）：
CREATE TABLE clienttable(
ClientID
SMALLINT(15)
NOT NULL
DEFAULT 0,
ClientFirstName
VARCHAR(30)
NOT NULL
DEFAULT "first name",
ClientLastName
VARCHAR(30)
NOT NULL
DEFAULT "last name",
ClientPhone
CHAR(10)
NOT NULL,
ClientEmail
TINYTEXT(30) //Line 18 <- This is where the error occurs
NULL);
Yes, it's not highly readable, but if we run it, we get a syntax error at line 18, i.e. the TINYTEXT(30)line. Reading the documentation, asking online, etc. would show you wy this is wrong. Once all the errors are fixed, make the code readable again and you're set.
是的，它的可读性不高，但是如果我们运行它，我们会得到一个语法错误at line 18，即TINYTEXT(30)行。阅读文档、在线询问等会告诉您这是错误的。修复所有错误后，再次使代码可读，然后就设置好了。
