In some cases, MySQL silently changes column specifications from
those given in a CREATE TABLE
or
ALTER TABLE
statement. These
might be changes to a data type, to attributes associated with a
data type, or to an index specification.
TIMESTAMP
display sizes are
discarded.
Also note that TIMESTAMP
columns are NOT NULL
by default.
Columns that are part of a PRIMARY KEY
are made NOT NULL
even if not declared
that way.
Trailing spaces are automatically deleted from
ENUM
and
SET
member values when the
table is created.
MySQL maps certain data types used by other SQL database vendors to MySQL types. See Section 10.7, “Using Data Types from Other Database Engines”.
If you include a USING
clause to specify
an index type that is not legal for a given storage engine,
but there is another index type available that the engine
can use without affecting query results, the engine uses the
available type.
If strict SQL mode is not enabled, a
VARCHAR
column with a length
specification greater than 65535 is converted to
TEXT
, and a
VARBINARY
column with a
length specification greater than 65535 is converted to
BLOB
. Otherwise, an error
occurs in either of these cases.
Specifying the CHARACTER SET binary
attribute for a character data type causes the column to be
created as the corresponding binary data type:
CHAR
becomes
BINARY
,
VARCHAR
becomes
VARBINARY
, and
TEXT
becomes
BLOB
. For the
ENUM
and
SET
data types, this does not
occur; they are created as declared. Suppose that you
specify a table using this definition:
CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );
The resulting table has this definition:
CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );
To see whether MySQL used a data type other than the one you
specified, issue a DESCRIBE
or
SHOW CREATE TABLE
statement after
creating or altering the table.
Certain other data type changes can occur if you compress a table using myisampack. See Section 13.5.3.3, “Compressed Table Characteristics”.
User Comments
Add your own comment.