Data Types (MySQL)
BOOLEAN are synonyms for
For more info see - http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
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.
||1970-01-01 00:00:01||2038-01-19 03:14:07||Stored as UTC (converted to during INSERT and from during SELECT)|
||1000-01-01 00:00:00||9999-12-31 23:59:59|
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 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 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.%';
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;