使用TINYTEXT列的CREATE TABLE语句中的语法错误1064？ [英] Syntax error 1064 in CREATE TABLE statement with TINYTEXT columns?

问题描述

这是我到目前为止的MySQL代码：

This is the MySQL code I have so far:

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)
);

在MySQL中在命令行上运行此代码会出现此错误：

Running this code in MySQL on the command line gives me this error:

错误1064（42000）：您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册，以获得正确的语法在第7行附近使用'（TypeID）
）'

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

我试过创建不同的测试表，省略NULL / NOT NULL，但似乎没有什么工作。任何帮助将非常，非常感谢！

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!

推荐答案

这是我看到与您的原始脚本的问题：

Here are the problems I see with your original script:

1. BLOB 和 TEXT 列不能有 DEFAULT 值。

TINYTEXT 与 VARCHAR（255）相同，因此您不能为 TINYTEXT 字段声明最大长度，因为一个已经暗示。

TINYTEXT is the same as VARCHAR(255), so you can't declare a maximum length for a TINYTEXT field because one is already implied.

PRIMARYKEY 之间需要一个空格。应该是 PRIMARY KEY 。

You need a space between the words PRIMARYKEY. It should be PRIMARY KEY.

最后，这不是一个问题，您的第一个 CREATE TABLE 语句，您有奇数间距。将 ClientEmail 行更改为可读性更好：

Finally, it isn't a problem per se, but in your first CREATE TABLE statement, you have odd spacing. Changing the ClientEmail line to the following makes it a lot more readable:

更好：

ClientPhone CHAR(10) NOT NULL,
ClientEmail TINYTEXT NOT NULL,

请参阅 MySQL文档以获取更多信息。所有这些更正后，这些都是正在运行的MySQL查询：

See the MySQL documentation for more information. After all of those corrections, these are the working MySQL queries:

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)
);

如果您点击 href =http://sqlfiddle.com/#!2/40854>这个SQL小提示，你会看到它的工作原理！

If you click Build Schema in this SQL fiddle, you'll see that it works!

如果你是有条理的，这些问题很容易解决，特别是 CREATE TABLE 语句。例如，当您调试第一个 CREATE TABLE 语句时，请及时移动列声明。

If you're methodical, these problems are easy to solve, especially with CREATE TABLE statements. For example, when you're debugging the first CREATE TABLE statement, move through the column declarations one at at time.

尝试使用第一列创建表格：

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:

错误1064（42000）：您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册，以获得在第7行使用的（30）NULL附近的正确语法。

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

现在你知道哪一行有错误。我偶尔看到了类似这样的代码（这与上面的例子中的代码相同）：

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);

是的，它的可读性不高，但如果我们运行它， $ c>在第18行，即 TINYTEXT（30）行。阅读文档，在线询问等会告诉你这是错误的。一旦所有的错误都是固定的，让代码可读又重新设置。

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.

这篇关于使用TINYTEXT列的CREATE TABLE语句中的语法错误1064？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！