Next I'll describe the physical attributes of each column in a row. The format depends entirely on the data type and the size of the column, so, for every data type, I'll give a description and an example.
All the types are defined within the include/mysql_com.h file
within the enum_field_types
enumerated
structure. Here's a sample of the key values and corresponding
numbers:
MYSQL_TYPE_BIT 16 MYSQL_TYPE_BLOB 252 MYSQL_TYPE_DATE 10 MYSQL_TYPE_DATETIME 12 MYSQL_TYPE_DECIMAL 0 MYSQL_TYPE_DOUBLE 5 MYSQL_TYPE_ENUM 247 MYSQL_TYPE_FLOAT 4 MYSQL_TYPE_GEOMETRY 255 MYSQL_TYPE_INT24 9 MYSQL_TYPE_LONG 3 MYSQL_TYPE_LONGLONG 8 MYSQL_TYPE_LONG_BLOB 251 MYSQL_TYPE_MEDIUM_BLOB 250 MYSQL_TYPE_NEWDATE 14 MYSQL_TYPE_NEWDECIMAL 246 MYSQL_TYPE_NULL 6 MYSQL_TYPE_SET 248 MYSQL_TYPE_SHORT 2 MYSQL_TYPE_STRING 254 MYSQL_TYPE_TIME 11 MYSQL_TYPE_TIMESTAMP 7 MYSQL_TYPE_TINY 1 MYSQL_TYPE_TINY_BLOB 249 MYSQL_TYPE_VARCHAR 15 MYSQL_TYPE_VAR_STRING 253 MYSQL_TYPE_YEAR 13
The character data types
CHAR
-
Storage: fixed-length string with space padding on the right.
Example: a
CHAR(5)
column containing the value'A'
looks like:hexadecimal 41 20 20 20 20
-- (length = A??'</code>)
VARCHAR
Storage: variable-length string with a preceding length.
Example: a
VARCHAR(7)
column containing'A'
looks like:hexadecimal 01 41
-- (length = A'</code>)In MySQL 4.1 the length is always 1 byte. In MySQL 5.0 the length may be either 1 byte (for up to 255) or 2 bytes (for 256 to 65535). Some further random notes about the new format: In old tables (from MySQL 4.1 and earlier),
VARCHAR
columns have typeMYSQL_TYPE_VAR_STRING
, which works exactly like aCHAR
with the exception that if you do anALTER TABLE
, it's converted to a trueVARCHAR
(MYSQL_TYPE_VARCHAR
). (This means that old tables will work as before for users.) ... Apart from the above case, there are no longer any automatic changes fromCHAR
toVARCHAR
or fromVARCHAR
toCHAR
. MySQL will remember the declared type and stick to it ...VARCHAR
is implemented infield.h
andfield.cc
through the new classField_varstring
...MyISAM
implementsVARCHAR
both for dynamic-length and fixed-length rows (as signaled with theROW_FORMAT
flag) ...VARCHAR
now stores trailing spaces. (If they don't fit, that's an error in strict mode.) Trailing spaces are not significant in comparisons ... Intable->record
, the space is reserved for length (1 or 2 bytes) plus data ... The number of bytes used to store the length is in the fieldField_varchar->length_bytes
. Note that internally this can be 2 even ifField_varchar->field_length
< 256 (for example, for a shortened key to avarchar(256)
) ... There is a new macro,HA_VARCHAR_PACKLENGTH(field_length)
, that can be used onfield->length
in write_row / read_row to check how many length bytes are used. (In this context we can't have a field_length < 256 with a 2-byte pack length) ... When creating a key for the handler,HA_KEYTYPE_VARTEXT1
andHA_KEYTYPE_BINARY1
are used for a key on a column that has a 1-byte length prefix andHA_KEYTYPE_VARTEXT2
andHA_KEYTYPE_BINARY2
for a column that has a 2-byte length prefix. (In the future we will probably deleteHA_KEYTYPE_BINARY#
, as this can be instead be done by just using thebinary
character set withHA_KEYTYPE_VARTEXT#
.) ... When sending a key to the handler forindex_read()
or records_in_range, we always use a 2-byte length for theVARCHAR
to make things simpler. (For version 5.1 we intend to changeCHAR
s to also use a 2-byte length for these functions, as this will speed up and simplify the key handling code on the handler side.) ... The test case filemysql-test/include/varchar.inc
should be included in the code that tests the handler. Seet/myisam.test
for how to use this. You should verify the result against the one inmysql-test/t/myisam.result
to ensure that you get the correct results ... A client sees both the old and newVARCHAR
type asMYSQL_TYPE_VAR_STRING
. It will never (at least for 5.0) seeMYSQL_TYPE_VARCHAR
. This ensures that old clients will work as before ... If you run MySQL 5.0 with the--new
option, MySQL will show oldVARCHAR
columns as'CHAR'
inSHOW CREATE TABLE
. (This is useful when testing whether a table is using the newVARCHAR
type or not.)
-
The numeric data types
Important: MySQL almost always stores multi-byte binary numbers with the low byte first. This is called "little-endian" numeric storage; it's normal on Intel x86 machines; MySQL uses it even for non-Intel machines so that databases will be portable.
TINYINT
Storage: fixed-length binary, always one byte.
Example: a
TINYINT
column containing65
looks like:hexadecimal 41
-- (length = 1, value = 65)
SMALLINT
Storage: fixed-length binary, always two bytes.
Example: a
SMALLINT
column containing65
looks like:hexadecimal 41 00
-- (length = 2, value = 65)
MEDIUMINT
Storage: fixed-length binary, always three bytes.
Example: a
MEDIUMINT
column containing65
looks like:hexadecimal 41 00 00
-- (length = 3, value = 65)
INT
Storage: fixed-length binary, always four bytes.
Example: an
INT
column containing65
looks like:hexadecimal 41 00 00 00
-- (length = 4, value = 65)
BIGINT
Storage: fixed-length binary, always eight bytes.
Example: a
BIGINT
column containing65
looks like:hexadecimal 41 00 00 00 00 00 00 00
-- (length = 8, value = 65)
FLOAT
Storage: fixed-length binary, always four bytes.
Example: a
FLOAT
column containing approximately65
looks like:hexadecimal 00 00 82 42
-- (length = 4, value = 65)
DOUBLE PRECISION
Storage: fixed-length binary, always eight bytes.
Example: a
DOUBLE PRECISION
column containing approximately65
looks like:hexadecimal 00 00 00 00 00 40 50 40
-- (length = 8, value = 65)
REAL
Storage: same as
FLOAT
, or same asDOUBLE PRECISION
, depending on the setting of the--ansi
option.
DECIMAL
MySQL 4.1 Storage: fixed-length string, with a leading byte for the sign, if any.
Example: a
DECIMAL(2)
column containing65
looks like:hexadecimal 20 36 35
-- (length = 3, value =' 65'
)Example: a
DECIMAL(2) UNSIGNED
column containing65
looks like:hexadecimal 36 35
-- (length = 2, value ='65'
)Example: a
DECIMAL(4,2) UNSIGNED
column containing65
looks like:hexadecimal 36 35 2E 30 30
-- (length = 5, value ='65.00'
)MySQL 5.0 Storage: high byte first, four-byte chunks. We call the four-byte chunks "*decimal* digits". Since 2**32 = There is an implied decimal point. Details are in /strings/decimal.c.
Example: a MySQL 5.0
DECIMAL(21,9)
column containing111222333444.555666777
looks like:hexadecimal 80 6f 0d 40 8a 04 21 1e cd 59
-- (flag + '111', '222333444', '555666777').
NUMERIC
Storage: same as
DECIMAL
.
BOOL
Storage: same as
TINYINT
.
-
The temporal data types
DATE
Storage: 3 byte integer, low byte first. Packed as: 'day + month*32 + year*16*32'
Example: a
DATE
column containing'1962-01-02'
looks like:hexadecimal 22 54 0F
DATETIME
Storage: eight bytes.
Part 1 is a 32-bit integer containing year*10000 + month*100 + day.
Part 2 is a 32-bit integer containing hour*10000 + minute*100 + second.
Example: a
DATETIME
column for'0001-01-01 01:01:01'
looks like:hexadecimal B5 2E 11 5A 02 00 00 00
TIME
Storage: 3 bytes, low byte first. This is stored as seconds: days*24*3600+hours*3600+minutes*60+seconds
Example: a
TIME
column containing'1 02:03:04'
(1 day 2 hour 3 minutes and 4 seconds) looks like:hexadecimal 58 6E 01
TIMESTAMP
Storage: 4 bytes, low byte first. Stored as unix
time()
, which is seconds since the Epoch (00:00:00 UTC, January 1, 1970).Example: a
TIMESTAMP
column containing'2003-01-01 01:01:01'
looks like:hexadecimal 4D AE 12 23
YEAR
Storage: same as unsigned
TINYINT
with a base value of 0 = 1901.
-
Others
SET
Storage: one byte for each eight members in the set.
Maximum length: eight bytes (for maximum 64 members).
This is a bit list. The least significant bit corresponds to the first listed member of the set.
Example: a
SET('A','B','C')
column containing'A'
looks like:01
-- (length = 1, value = 'A')
ENUM
Storage: one byte if less than 256 alternatives, else two bytes.
This is an index. The value 1 corresponds to the first listed alternative. (Note:
ENUM
always reserves the value 0 for an erroneous value. This explains why'A'
is 1 instead of 0.)Example: an
ENUM('A','B','C')
column containing'A'
looks like:01
-- (length = A')
The Large-Object data types
Warning: Because TINYBLOB
's preceding length
is one byte long (the size of a TINYINT
) and
MEDIUMBLOB
's preceding length is three bytes
long (the size of a MEDIUMINT
), it's easy to
think there's some sort of correspondence between the
BLOB
and INT
types. There
isn't a BLOB
's preceding length is not four
bytes long (the size of an INT
).
TINYBLOB
-
Storage: variable-length string with a preceding one-byte length.
Example: a
TINYBLOB
column containing'A'
looks like:hexadecimal 01 41
-- (length = A')
TINYTEXT
Storage: same as
TINYBLOB
.
BLOB
Storage: variable-length string with a preceding two-byte length.
Example: a
BLOB
column containing'A'
looks like:hexadecimal 01 00 41
-- (length = A')
TEXT
Storage: same as
BLOB
.
MEDIUMBLOB
Storage: variable-length string with a preceding length.
Example: a
MEDIUMBLOB
column containing'
A' looks like:hexadecimal 01 00 00 41
-- (length = A')
MEDIUMTEXT
Storage: same as
MEDIUMBLOB
.
LONGBLOB
Storage: variable-length string with a preceding four-byte length.
Example: a
LONGBLOB
column containing'A'
looks like:hexadecimal 01 00 00 00 41
-- (length = A')
LONGTEXT
Storage: same as
LONGBLOB
.