Description:
MySQL Manual ref: 2006-01-28 (revision: 1077)
Hi all.
I'm using mysql 5.0.18-standard-log and getting my head
around the mysql manual, and I note that the create table
syntax is not correct.
There are quite a few places where the syntax in this
section does not actually reflect the syntax mysql uses,
according to the conventions noted in the manual.
As the mysql manual is the canonical reference on mysql and it's syntax,
I think it is very important that the mysql syntax documented in the
manual is correct and up to date for the actual implementation of mysql.
<Quote>
1.2. Conventions Used in This Manual
In syntax descriptions, square brackets ( [ and ] ) indicate
optional words or clauses. For example, in the following
statement, IF EXISTS is optional:
DROP TABLE [IF EXISTS] tbl_name
When a syntax element consists of a number of alternatives,
the alternatives are separated by vertical bars (|).
When one member from a set of choices *may* be chosen, the
alternatives are listed within square brackets ( [ and ] ):
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
When one member from a set of choices *must* be chosen, the
alternatives are listed within braces ( { and } ):
{DESCRIBE | DESC} tbl_name [col_name | wild]
An ellipsis (...) indicates the omission of a section of a
statement, typically to provide a shorter version of more
complex syntax. For example, INSERT ... SELECT is shorthand
for the form of INSERT statement that is followed by a
SELECT statement.
An ellipsis can also indicate that the preceding syntax
element of a statement may be repeated. In the following
example, multiple reset_option values may be given, with
each of those after the first preceded by commas:
RESET reset_option [,reset_option] ...
</Quote>
Bearing in mind the above manual syntax conventions, and
looking at the create table syntax, we have:
(Copied and pasted directly from the docs)
13.1.5. CREATE TABLE Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options] [select_statement]
So if I read the syntax above correctly, according to the manual conventions,
the above syntax tells me that the following is legal:
mysql> create table t1;
ERROR 1113 (42000): A table must have at least 1 column
So surely the create_definition is required, and must not be
enclosed in [] brackets, indicating that it is optional?
create_definition:
column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
| KEY [index_name] [index_type] (index_col_name,...)
| INDEX [index_name] [index_type] (index_col_name,...)
| [CONSTRAINT [symbol]] UNIQUE [INDEX]
[index_name] [index_type] (index_col_name,...)
| [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
again the above line says that FULLTEXT or SPATIAL are
optional choices, so if I do:
mysql> create table t1 (
-> a text,
-> (a)
-> );
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 '(a)
)' at line 3
I get an error.
If I do:
mysql> create table t1 (
-> a text,
-> fulltext (a)
-> );
Query OK, 0 rows affected (0.04 sec)
There is no error.
So the [FULLTEXT | SPATIAL] is not an optional choice, but a
required choice, and should be enclosed in {} brackets to
indicate this, such as {FULLTEXT|SPATIAL}.
column_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| BINARY(length)
| VARBINARY(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
According to the column_definition syntax above, I should be
able to use any type from the type list, and suffix it with
auto_increment.
So if I do:
mysql> create table t1 (
-> a_date date not null auto_increment
-> );
ERROR 1063 (42000): Incorrect column specifier for column
'a_date'
Obviously, you cannot auto_increment a date column, but the
docs say that you can!
I would like to suggest that the CREATE TABLE column_definition is
re-written with several sub-types that would list the correct optional
(or required) clauses/attributes for each of those sub-types, such as:
column_definition:
col_name int_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
| col_name real_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
| col_name date_type [NOT NULL | NULL] [DEFAULT default_value]
[UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definit
ion]
| col_name char_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
| col_name text_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
| col_name set_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]
int_type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
| INT[(length)] [UNSIGNED] [ZEROFILL]
| INTEGER[(length)] [UNSIGNED] [ZEROFILL]
| BIGINT[(length)] [UNSIGNED] [ZEROFILL]
real_type
| REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
| FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
| DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
| NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
date_type
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
char_type
| CHAR(length) [BINARY | ASCII | UNICODE]
| VARCHAR(length) [BINARY]
| BINARY(length)
| VARBINARY(length)
text_type
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
set_type
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
I have not changed the clauses/attributes for each sub-type
column_definition option above, apart from removing the [AUTO_INCREMENT]
attribute from the date_type option.
I need to get my head around and fully understand:
11. Data Types
13. SQL Statement Syntax
From the manual first.
Hopefully, if the documentation for mysql syntax is 100% correct, this will
give mysql users a quick overview of all mysql syntax available, and reduce the need
for further text discussions on what is, or is not, legal mysql syntax.
Kind Regards
Keith Roberts
In theory, theory and practice are the same;
in practice they are not.
How to repeat:
Please see the relevant section of the manual - 13.1.5. CREATE TABLE Syntax
Suggested fix:
re-write the relevant section so it matches the mysql syntax correctly.
Description: MySQL Manual ref: 2006-01-28 (revision: 1077) Hi all. I'm using mysql 5.0.18-standard-log and getting my head around the mysql manual, and I note that the create table syntax is not correct. There are quite a few places where the syntax in this section does not actually reflect the syntax mysql uses, according to the conventions noted in the manual. As the mysql manual is the canonical reference on mysql and it's syntax, I think it is very important that the mysql syntax documented in the manual is correct and up to date for the actual implementation of mysql. <Quote> 1.2. Conventions Used in This Manual In syntax descriptions, square brackets ( [ and ] ) indicate optional words or clauses. For example, in the following statement, IF EXISTS is optional: DROP TABLE [IF EXISTS] tbl_name When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (|). When one member from a set of choices *may* be chosen, the alternatives are listed within square brackets ( [ and ] ): TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) When one member from a set of choices *must* be chosen, the alternatives are listed within braces ( { and } ): {DESCRIBE | DESC} tbl_name [col_name | wild] An ellipsis (...) indicates the omission of a section of a statement, typically to provide a shorter version of more complex syntax. For example, INSERT ... SELECT is shorthand for the form of INSERT statement that is followed by a SELECT statement. An ellipsis can also indicate that the preceding syntax element of a statement may be repeated. In the following example, multiple reset_option values may be given, with each of those after the first preceded by commas: RESET reset_option [,reset_option] ... </Quote> Bearing in mind the above manual syntax conventions, and looking at the create table syntax, we have: (Copied and pasted directly from the docs) 13.1.5. CREATE TABLE Syntax CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] So if I read the syntax above correctly, according to the manual conventions, the above syntax tells me that the following is legal: mysql> create table t1; ERROR 1113 (42000): A table must have at least 1 column So surely the create_definition is required, and must not be enclosed in [] brackets, indicating that it is optional? create_definition: column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) | KEY [index_name] [index_type] (index_col_name,...) | INDEX [index_name] [index_type] (index_col_name,...) | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] [index_type] (index_col_name,...) | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...) again the above line says that FULLTEXT or SPATIAL are optional choices, so if I do: mysql> create table t1 ( -> a text, -> (a) -> ); 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 '(a) )' at line 3 I get an error. If I do: mysql> create table t1 ( -> a text, -> fulltext (a) -> ); Query OK, 0 rows affected (0.04 sec) There is no error. So the [FULLTEXT | SPATIAL] is not an optional choice, but a required choice, and should be enclosed in {} brackets to indicate this, such as {FULLTEXT|SPATIAL}. column_definition: col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR(length) [BINARY | ASCII | UNICODE] | VARCHAR(length) [BINARY] | BINARY(length) | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] | TEXT [BINARY] | MEDIUMTEXT [BINARY] | LONGTEXT [BINARY] | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) | spatial_type According to the column_definition syntax above, I should be able to use any type from the type list, and suffix it with auto_increment. So if I do: mysql> create table t1 ( -> a_date date not null auto_increment -> ); ERROR 1063 (42000): Incorrect column specifier for column 'a_date' Obviously, you cannot auto_increment a date column, but the docs say that you can! I would like to suggest that the CREATE TABLE column_definition is re-written with several sub-types that would list the correct optional (or required) clauses/attributes for each of those sub-types, such as: column_definition: col_name int_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] | col_name real_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] | col_name date_type [NOT NULL | NULL] [DEFAULT default_value] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definit ion] | col_name char_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] | col_name text_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] | col_name set_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [reference_definition] int_type: TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] real_type | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL] | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL] date_type | DATE | TIME | TIMESTAMP | DATETIME | YEAR char_type | CHAR(length) [BINARY | ASCII | UNICODE] | VARCHAR(length) [BINARY] | BINARY(length) | VARBINARY(length) text_type | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] | TEXT [BINARY] | MEDIUMTEXT [BINARY] | LONGTEXT [BINARY] set_type | ENUM(value1,value2,value3,...) | SET(value1,value2,value3,...) | spatial_type I have not changed the clauses/attributes for each sub-type column_definition option above, apart from removing the [AUTO_INCREMENT] attribute from the date_type option. I need to get my head around and fully understand: 11. Data Types 13. SQL Statement Syntax From the manual first. Hopefully, if the documentation for mysql syntax is 100% correct, this will give mysql users a quick overview of all mysql syntax available, and reduce the need for further text discussions on what is, or is not, legal mysql syntax. Kind Regards Keith Roberts In theory, theory and practice are the same; in practice they are not. How to repeat: Please see the relevant section of the manual - 13.1.5. CREATE TABLE Syntax Suggested fix: re-write the relevant section so it matches the mysql syntax correctly.