Data Types (MySQL)
Numbers
BOOL
and BOOLEAN
are synonyms for TINYINT(1)
.
Integers
Type | Bytes | Min | Max |
---|---|---|---|
TINYINT |
1 | -128 | 127 |
TINYINT UNSIGNED |
1 | 0 | 255 |
SMALLINT |
2 | -32768 | 32767 |
SMALLINT UNSIGNED |
2 | 0 | 65535 |
MEDIUMINT |
3 | -8388608 | 8388607 |
MEDIUMINT UNSIGNED |
3 | 0 | 16777215 |
INT |
4 | -2147483648 | 2147483647 |
INT UNSIGNED |
4 | 0 | 4294967295 |
BIGINT |
8 | -9223372036854775808 | 9223372036854775807 |
BIGINT UNSIGNED |
8 | 0 | 18446744073709551615 |
For more info see - http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
Strings
There's two main string types
VARCHAR
- Variable character length up a prescribed maximum (egVARCHAR(32)
). Max allowed is 65,535 (though in reality this max width is shared across all columns so is less in practice.CHAR
- Fixed character length up a prescribed maximum (egCHAR(32)
). Max allowed is 255
Dates and Times
Date and time values need to specified in the general format of YYYY-MM-DD HH:MM:SS
(date or time components should be omitted if required for the table column. MySQL is relaxed on the usage of delimiters, so YYYY^MM^DD HH-MM-SS
, or YYYYMMDDHHMMSS
should be fine so long as the overall order of year, month, day, etc is correct and the values are valid.
Type | Min | Max | Comments |
---|---|---|---|
DATE |
1000-01-01 | 9999-12-31 | |
TIME |
-838:59:59 | 838:59:59 | |
TIMESTAMP |
1970-01-01 00:00:01 | 2038-01-19 03:14:07 | Stored as UTC (converted to during INSERT and from during SELECT) |
DATETIME |
1000-01-01 00:00:00 | 9999-12-31 23:59:59 | |
YEAR |
1901 | 2155 |
When querying data for use in PHP scripts, DATETIME values need to be converted into Unix Timestamp, for example...
SELECT UNIX_TIMESTAMP(datetime) AS datetime FROM table;
NULL
NULL means "no data", it doesn't mean zero. Therefore 0 <> NULL in an numerical field, and "NULL" <> NULL in a string field.
To set a field to NULL, use NULL without any quotes eg...
INSERT INTO table (col1, col2) VALUES ('data1', NULL);
IP Addresses
IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable, but it is beneficial in as much as that if you use a SELECT ... ORDER BY ip
type of statement the IP's will be correctly sorted
MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');
SELECT INET_NTOA(ip) FROM ips;
SELECT INET_NTOA(ip) FROM ips WHERE INET_NTOA(ip) LIKE '10.1.2.%';
Alternatively, use VARCHAR(15)
to store as text.
The data returned by INET_NTOA()
is in binary string format, which can occasionally cause problems. If you're passing the data into PowerShell, for example, you end up by a [byte]
object that is nigh on impossible to convert to a standard string. To force MySQL to return a string wrap the command in CONVERT(x, CHAR)
, eg
SELECT CONVERT(INET_NTOA(ip), CHAR) AS ip FROM ips;