:: Intro :: Datatypes :: DDL :: DML :: Operators :: Built-ins ::

These vary from release to release, but typically refer to DATA, DATABASE and RESERVED WORDS

Data Types

MySQL works with information that appears and behaves as though it is stored in TABLES. These tables have columns and these columns have specific data types. Types of data are chosen to suit the purpose (ie. based on what you want to store in the columns).

A Complete list of all supported data types is available from MYSQL. Some of the more common Data Types available in MySQL have been swiped from the manual and are as follows ("M" stands for Maximum length, "D" stands for Decimal places in numerical data types):

Whole : Real : Date/Time : Text

Whole Numbers

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

Decimal Numbers

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. The M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(X) where X <= 24 stands for a single-precision floating-point number.

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

REAL A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. If UNSIGNED is specified, negative values are disallowed. The M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number.

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

An unpacked floating-point number. Behaves like a CHAR column: ``unpacked'' means the number is stored as a string, using one character for each digit of the value. The decimal point and, for negative numbers, the `-' sign, are not counted in M (but space for these is reserved). If D is 0, values will have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If UNSIGNED is specified, negative values are disallowed. If D is omitted, the default is 0. If M is omitted, the default is 10. Prior to MySQL Version 3.23, the M argument must include the space needed for the sign and the decimal point.

Date/Time

DATE

A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.

DATETIME

A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.

TIME

A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers.

YEAR[(2|4)]

A year in 2- or 4-digit format (default is 4-digit). The allowable values are 1901 to 2155, 0000 in the 4-digit year format, and 1970-2069 if you use the 2-digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. To get the current year in 4 digit form, you can use the year(now()) expression.

Text

CHAR(M) [BINARY]

A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 0 to 255 characters. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given.

VARCHAR(M) [BINARY]

A variable-length string. The range of M is 0 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given.

BLOB

TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. There are many types of 'blob' columns and they can be used to store Binary Large OBjects (ie. files).

ENUM('value1','value2',...)

An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special "" error value. An ENUM can have a maximum of 65535 distinct values.

SET('value1','value2',...)

A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET can have a maximum of 64 members.

Column type	Storage required
TINYINT	1 byte
SMALLINT	2 bytes
MEDIUMINT	3 bytes
INTEGER	4 bytes
BIGINT	8 bytes
FLOAT(X)	4 if X <= 24 or 8 if 25 <= X <= 53
FLOAT	4 bytes
DOUBLE	8 bytes
DECIMAL(M,D)	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
NUMERIC(M,D)	M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)
DATE	3 bytes
DATETIME	8 bytes
TIME	3 bytes
YEAR	1 byte
CHAR(M)	M bytes, 1 <= M <= 255
VARCHAR(M)	L+1 bytes, where L <= M and 1 <= M <= 255
BLOB, TEXT	L+2 bytes, where L < 2^16
ENUM('value1','value2',...)	1 or 2 bytes, depending on the number of enumeration values (65535 values maximum)
SET('value1','value2',...)	1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum)

Table/Database Limits

Maximum table sizes (stored) for MySQL are around 4Gb - that is a LOT of data. With tweaking on FreeBSD Unix, tables of up to 8Tb (8 mega-gigabytes) are possible ... sensible?

Column, Table and Database names are limited to 64 characters (and you should avoid using the '/', '\' and '.' characters in these names as they have reserved meanings

Reserved Words

Reserved words are those that can be used for no other purpose but that which they are defined for.

ADD	ALL	ALTER
ANALYZE	AND	AS
ASC	AUTO_INCREMENT	BDB
BERKELEYDB	BETWEEN	BIGINT
BINARY	BLOB	BOTH
BTREE	BY	CASCADE
CASE	CHANGE	CHAR
CHARACTER	COLLATE	COLUMN
COLUMNS	CONSTRAINT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME
CURRENT_TIMESTAMP	DATABASE	DATABASES
DAY_HOUR	DAY_MINUTE	DAY_SECOND
DEC	DECIMAL	DEFAULT
DELAYED	DELETE	DESC
DESCRIBE	DISTINCT	DISTINCTROW
DOUBLE	DROP	ELSE
ENCLOSED	ERRORS	ESCAPED
EXISTS	EXPLAIN	FIELDS
FLOAT	FOR	FOREIGN
FROM	FULLTEXT	FUNCTION
GEOMETRY	GRANT	GROUP
HASH	HAVING	HELP
HIGH_PRIORITY	HOUR_MINUTE	HOUR_SECOND
IF	IGNORE	IN
INDEX	INFILE	INNER
INNODB	INSERT	INT
INTEGER	INTERVAL	INTO
IS	JOIN	KEY
KEYS	KILL	LEADING
LEFT	LIKE	LIMIT
LINES	LOAD	LOCK
LONG	LONGBLOB	LONGTEXT
LOW_PRIORITY	MASTER_SERVER_ID	MATCH
MEDIUMBLOB	MEDIUMINT	MEDIUMTEXT
MIDDLEINT	MINUTE_SECOND	MRG_MYISAM
NATURAL	NOT	NULL
NUMERIC	ON	OPTIMIZE
OPTION	OPTIONALLY	OR
ORDER	OUTER	OUTFILE
PRECISION	PRIMARY	PRIVILEGES
PROCEDURE	PURGE	READ
REAL	REFERENCES	REGEXP
RENAME	REPLACE	REQUIRE
RESTRICT	RETURNS	REVOKE
RIGHT	RLIKE	RTREE
SELECT	SET	SHOW
SMALLINT	SONAME	SPATIAL
SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS	SQL_SMALL_RESULT
SSL	STARTING	STRAIGHT_JOIN
STRIPED	TABLE	TABLES
TERMINATED	THEN	TINYBLOB
TINYINT	TINYTEXT	TO
TRAILING	TYPES	UNION
UNIQUE	UNLOCK	UNSIGNED
UPDATE	USAGE	USE
USER_RESOURCES	USING	VALUES
VARBINARY	VARCHAR	VARYING
WARNINGS	WHEN	WHERE
WITH	WRITE	XOR
YEAR_MONTH	ZEROFILL

Rather a lot, I am afraid - these words make lousy choices as column names, table or database names and will result in odd errors with less than helpful error messages if used in this manner.