In the above table L means the actual length of a instance and M the maximum length. So L+1 for "abcd" means 5 bytes in the database.
If you use any data type with an L in the length field you will get a variable length record format.
All integer types can have an optional argument unsigned. This
can be used when you only want to allow positive numbers in the column
or you need a little bigger numerical range for the column.
Also for all integer columnsn, the optional argument ZEROFILL means
that the column will be padded with zeroes up to the maximum length.
Max display size and decimals are for formatting and calculation of maximum column width.
When storing a value in an integer that is outside its range,
MySQL stores the maximum (or minimum) possible value. When
doing an ALTER TABLE or LOAD DATA INFILE one gets these
conversions as 'warnings'. We have on the TODO to fix INSERT and
UPDATE so they can return warnings, but this is scheduled for the next
protocol change.
For example when storing -999999999999999 into an int column the
value ends up as -2147483648. And 9999999999999999 ends up
as 2147483647.
And if the int is unsigned the stored values above becomes 0 and
4294967296.
The same rules go for all other integer types.
When returning data for an int(4) column that exceeds the allocated space,
MySQL will return 9.99. If the operation is an UPDATE
a warning will be issued.
Note that a type like decimal(4,2) means maximum 4 characters
with two decimal points. That gives a range between -.99 ->
9.99.
To avoid some rounding problems, MySQL always rounds everything
that it stores in any floating point column according to the number of
decimals. This means that 2.333 stored into float(8,2) is
stored as 2.33.
TIMESTAMP type
Has a range of 1 Dec 1970 time 0.00 to sometime in the year 2106 and a
resolution of one second. A TIMESTAMP column will automatically be updated
on INSERT and UPDATE statements if set to NULL or if
the column is not updated in the statement. Can be (part of) an index.
Note that if you have many timestamp columns in a row, then only the
first timestamp column will be automatically updated. Any timestamp column
will be set to the current time if set to NULL. Depending on
the display size one gets one of the following formats: "YYYY-MM-DD HH:MM:SS",
"YY-MM-DD HH:MM:SS", "YYYY-MM-DD" or "YY-MM-DD".
TEXT and BLOB types
These are objects that can have a variable length without upper limit.
All TEXT and BLOB objects are stored with their length (saved in 1 to 4
bytes depending on the type of object). The maximum TEXT and
BLOB length you can use is dependent on available memory and
client buffers. The only differences between TEXT and BLOB
is that TEXT is sorted and compared case insensitively while
BLOB is compared case sensitively (by character
values). TEXT and BLOB objects CANNOT be an index.
A BLOB is a binary large object which can hold any amount of data. There are 4 kinds of blobs See section Column types.. Normally one can regard a BLOB as a VARCHAR without a specified limit.
TEXT is a BLOB that is sorted and compared case
insensitively.
A BLOB/TEXT column may not be bigger that the message
buffer. Note that you have to change the message buffer on both the
server and the client. See section How does one change the size of MySQL buffers?.
MyODBC defines BLOBs as LONGVARBINARY and
TEXTs as LONGVARCHAR.
Restrictions for BLOB and TEXT columns:
BLOB or TEXT cannot be an index or a part of an index
BLOB or TEXT, only the first
max_sort_length (default 1024) of the blob is used. This value
can be changed by the -O option when starting the mysqld
daemon. One can group on an expression involving a BLOB/
TEXT: SELECT id,SUBSTR(blob,1,100) GROUP BY 2
BLOB and TEXT as
there is for CHAR and VARCHAR.
ENUM type
A string object that can have only one of a set of allowed values. The value
to be stored may be given case independently. If one tries to store a
non-existing value, "" is stored. If used in a number context this
object returns/stores the value index. If there is less than 255
possible values this object occupies 1 byte, else two bytes (with a
maximum of 65535 different values). Note that if an integer is put in the
ENUM you get the corresponding string with the first counting as
number 1. (0 is reserved for wrong enum values). Sorting on ENUM
types are done according to the order of the strings in the enum.
If declared NOT NULL the default value is the first value, else
the default value is NULL.
For example the column test ENUM("one","two", "three") can have
any of these values:
NULL "one" "two" "three"
SET type
A string object that can have one or many values from a set of allowed
values. Each value is separated by a ','. If used in a number context
this object returns/stores the bit positions of the used values. This
object occupies (number_of_different_values-1)/8+1 bytes, rounded up to
1,2,3,4 or 8. One can't have more than 64 different values. Note that if
an integer is put in the SET you get the corresponding string with
the first bit corresponding to the first string. Sorting on SET
types are done numerically.
For example the column test SET("one","two") NOT NULL can have any of
these values:
"" "one" "two" "one,two"
Normally on SELECT on a SET column with LIKE or FIND_IN_SET():
SELECT * from banner where banner_group LIKE '%value%';
SELECT * from banner where FIND_IN_SET('value',banner_group)>0;
But the following will also work:
SELECT * from banner where banner_group = 'v1,v2'; ;Exact match SELECT * from banner where banner_group & 1; ;Is in first group
Go to the first, previous, next, last section, table of contents.