Data Types (MySQL)

From vWiki
Jump to navigation Jump to search

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;