Data Types (MySQL)

Numbers

BOOL and BOOLEAN are synonyms for TINYINT(1) .

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 (eg VARCHAR(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 (eg CHAR(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;