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.
Possible data type changes are given in the following list.
VARCHAR
columns with a length
less than four are changed to
CHAR
.
If any column in a table has a variable length, the entire
row becomes variable-length as a result. Therefore, if a
table contains any variable-length columns
(VARCHAR
,
TEXT
, or
BLOB
), all
CHAR
columns longer than
three characters are changed to
VARCHAR
columns. This does
not affect how you use the columns in any way; in MySQL,
VARCHAR
is just a different
way to store characters. MySQL performs this conversion
because it saves space and makes table operations faster.
See Chapter 13, Storage Engines.
From MySQL 4.1.0 onward, a
CHAR
or
VARCHAR
column with a length
specification greater than 255 is converted to the smallest
TEXT
type that can hold
values of the given length. For example,
VARCHAR(500)
is converted to
TEXT
, and
VARCHAR(200000)
is converted to
MEDIUMTEXT
. Similar
conversions occur for BINARY
and VARBINARY
, except that
they are converted to a BLOB
type.
Note that these conversions result in a change in behavior with regard to treatment of trailing spaces.
From MySQL 4.1.2 on, 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 );
For a specification of
DECIMAL(
,
if M
,D
)M
is not larger than
D
, it is adjusted upward. For
example, DECIMAL(10,10)
becomes
DECIMAL(11,10)
.
Other silent column specification changes include modifications to attribute or index specifications:
TIMESTAMP
display sizes are
discarded from MySQL 4.1 on, due to changes made to the
TIMESTAMP
data type in that
version. Before MySQL 4.1,
TIMESTAMP
display sizes must
be even and in the range from 2 to 14. If you specify a
display size of 0 or greater than 14, the size is coerced to
14. Odd-valued sizes in the range from 1 to 13 are coerced
to the next higher even number.
Also note that, in MySQL 4.1 and later,
TIMESTAMP
columns are
NOT NULL
by default.
Before MySQL 4.1.6, you cannot store a literal
NULL
in a
TIMESTAMP
column; setting it
to NULL
sets it to the current date and
time. Because TIMESTAMP
columns behave this way, the NULL
and
NOT NULL
attributes do not apply in the
normal way and are ignored if you specify them.
DESCRIBE
always reports
that a tbl_name
TIMESTAMP
column can
be assigned NULL
values.
Columns that are part of a PRIMARY KEY
are made NOT NULL
even if not declared
that way.
Starting from MySQL 3.23.51, 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.
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.1.3.3, “Compressed Table Characteristics”.
User Comments
Add your own comment.