Understanding MySQL Tables

You have a lot of flexibility when it comes to setting up MySQL tables. To have your MySQL database operate as efficiently as possible, you want to have the columns be assigned to the most appropriate size and data type to hold the data you need to store.

Use the following tables as a reference to the different data types that can be assigned to your columns. Data types available for use in MySQL fall into these categories: numbers, time and date, and character strings. Here are a few things you need to know as you read these tables:

• The maximum display size for a column is 255 characters. An M data type option sets the number of characters that are displayed and, in most cases, stored for the column.

• There can be up to 30 digits following the decimal point for floating-point or fixed-point data types. A D option to a data type indicates the number of digits allowed for a number following the decimal point. (The value should be no more than two digits less than the value of the display size being used.)

• The UNSIGNED option (shown in brackets) indicates that only positive numbers are allowed in the column. This allows the column to hold larger positive numbers.

• The ZEROFILL option (shown in brackets) indicates that the data in the column will be padded with zeros. For example, the number 25 in a column with a data type of INTEGER(7) ZEROFILL would appear as 0000025. (Any ZEROFILL column automatically becomes UNSIGNED.)

• All values shown in brackets are optional.

• The parentheses shown around the (M) and (D) values are necessary if you enter either of those values. In other words, don’t type the brackets, but do type the parentheses.

Table 24-2 shows numeric data types that you can use with MYSQL.

Table 24-2: Numeric Data Types for Columns

Data Type	Description	Space Needed
BIGINT[(M)] [UNSIGNED] [ZEROFILL]	Can contain large integers with the following allowable values: -9223372036854775808 to 9223372036854775807 (signed) 0 to 18446744073709551615 (unsigned)	Uses 8 bytes.
DECIMAL[(M[,D])] [ZEROFILL]	Contains an unpacked fixed-point number (signed only). Each digit is stored as a single character. When you choose the display value (M), decimal points and minus signs are not counted in that value. The value of (M) is 10 by default. Setting D to zero (which is the default) causes only whole numbers to be used.	Uses M+2 bytes if D is greater than 0. Uses M+1 bytes if D is equal to 0.
DOUBLE[(M,D)] [ZEROFILL]	Contains a double-precision, floating- point number of an average size. Values that are allowed include: -1.7976931348623157E+308 to -2.2250738585072014E-308 0 2.2250738585072014E-308 to 1.7976931348623157E+308.	Uses 8 bytes.
DOUBLE PRECISION	Same as DOUBLE.	Same as DOUBLE.
FLOAT(X) [ZEROFILL]	Contains a floating-point number. For a single-precision floating-point number X can be less than or equal to 24. For a double-precision floating- point number, X can be between 25 and 53. The display size and number of decimals are undefined.	Uses 4 bytes if X is less than or equal to 24. Uses 8 bytes if X is greater than or equal to 25 and less than or equal to 53
FLOAT[(M,D)] [ZEROFILL]	Contains a single-precision floating- point number. Values that are allowed include: -3.402823466E+38 to -1.175494351E-38 0 1.175494351E-38 to 3.402823466E+38. If the display value (M) is less than or equal to 24, the number is a single- precision floating-point number.	Uses 4 bytes.
INT[(M)] [UNSIGNED] [ZEROFILL]	Contains an integer of normal size. The range is -2147483648 to 2147483647 if it's signed and 0 to 4294967295 if unsigned.	Uses 4 bytes.
INTEGER[(M)] [UNSIGNED] [ZEROFILL]	Same as INT.	Same as INT.
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]	Contains an integer of medium size. The range is -8388608 to 8388607 if it's signed and 0 to 16777215 if unsigned.	Uses 3 bytes.
NUMERIC(M,D) [ZEROFILL]	Same as DECIMAL.	Same as DECIMAL.
REAL	Same as DOUBLE.	Same as DOUBLE.
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]	Contains an integer of small size. The range is -32768 to 32767 if it's signed and 0 to 65535 if it's unsigned.	Uses 2 bytes.
TINYINT[(M)] [UNSIGNED] [ZEROFILL]	A very small integer, with a signed range of -128 to 127 and a 0 to 255 unsigned range.	Uses 1 byte.

The default format of dates in MySQL is YYYY-MM-DD, which stands for the year, month, and day. Any improperly formatted date or time values will be converted to zeros. Table 24-3 shows time and date data types that you can use with MySQL.

Table 24-3: Time/Date Data Types for Columns

Data Type	Description	Space Needed
DATE	Contains a date between the range of January 1, 1000 (1000-01-01) and December 31, 9999 (9999-12-31).	Uses 3 bytes.
DATETIME	Contains a combination of date and time between zero hour of January 1, 1000 (1000-01-01 00:00:00) and the last second of December 31, 9999 (9999-12-31 23:59:59).	Uses 8 bytes.
TIMESTAMP[(M)]	Contains a timestamp from between zero hour of January 1, 1970 (1970-01- 01 00:00:00) and a time in the year 2037. It is stored in the form: YYYYMMDDHHMMSS. Using (M), you can reduce the size of the TIMESTAMP displayed to less than the full 14 characters (though the full 4-byte TIMESTAMP is still stored).	Uses 4 bytes.
TIME	Contains a time between -838:59:59 and 838:59:59. The format of the field is in hours, minutes, and seconds (HH:MM:SS).	Uses 3 bytes.
YEAR[(2|4)]	Contains a year, represented by either two or four digits. For a four-digit year, YEAR means 1901–2155 (0000 is also allowed). For a two-digit year, the digits 70-69 can represent 1970-2069.	Uses 1 byte.

Table 24-4 shows string data types that you can use with MYSQL.

Table 24-4: String Data Types for Columns

Data Type	Description	Space Needed
BLOB	Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. Searches on a BLOB column are case-sensitive.	Uses up to L+2 bytes, where L is less than or equal to 65535.
[NATIONAL] CHAR(M) [BINARY]	Contains a character string of fixed length, with spaces padded to the right to meet the length. To display the value, the spaces are deleted. The value of (M) determines the number of characters (from 1 to 255). If the BINARY keyword is used, sorting of values is case-sensitive (it is case- insensitive by default). The NATIONAL keyword indicates that the default character set should be used.	Uses between 1 and 255 bytes, based on the value of (M).
ENUM('val1','val2',...)	Contains enumerated strings that are typically chosen from a list of values indicated when you create the column. For example, you set a column definition to ENUM("dog","cat","mouse"). Then, if you set the value of that column to "1" the value displayed would be "dog", "2" would be "cat" and "3" would be mouse. It lets you take a number as input and have a string as output. Up to 65535 values are allowed.	Uses either 1 byte (for up to about 255 values) or 2 bytes, (for up to 65535 values).
LONGBLOB	Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. LONGBLOB allows larger values than MEDIUMBLOB. Searches on a LONGBLOB column are case- sensitive.	Uses up to L+4 bytes, where L is less than or equal to 4294967295.
LONGTEXT	Same as LONGBLOB, except that searching is done on these columns in case-insensitive style.	Uses up to L+4 bytes, where L is less than or equal to 4294967295.
MEDIUMBLOB	Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. MEDIUMBLOB allows larger values than BLOB. Searches on a MEDIUMBLOB column are case- sensitive.	Uses up to L+3 bytes, where L is less than or equal to 16777215.
MEDIUMTEXT	Same as MEDIUMBLOB, except that searching is done on these columns in case-insensitive style.	Uses up to L+3 bytes, where L is less than or equal to 16777215.
SET('val1','val2',...)	Contains a set of values. A SET column can display zero or more values from the list of values contained in the SET column definition. Up to 64 members are allowed.	Uses 1, 2, 3, 4 or 8 bytes, varying based on how many of the up to 64 set members are used.
TEXT	Same as BLOB, except that searching is done on these columns in case- insensitive style.	Uses up to L+2 bytes, where L is less than or equal to 65535.
TINYBLOB	Contains a binary large object (BLOB) that varies in size, based on the actual value of the data, rather than on the maximum allowable size. TINYBLOB allows smaller values than BLOB. Searches on a TINYBLOB column are case-sensitive.	Uses up to L+1 bytes, where L is less than or equal to 255.
TINYTEXT	Same as TINYBLOB, except that searching is done on these columns in case-insensitive style.	Uses up to L+1 bytes, where L is less than or equal to 255.
[NATIONAL] VARCHAR(M) [BINARY]	Contains a character string of variable length, with no padded spaces added. The value of (M) determines the number of characters (from 1 to 255). If the BINARY keyword is used, sorting of values is case-sensitive (it is case- insensitive by default). The NATIONAL keyword indicates that the default character set should be used.	Uses L+1 bytes, where L is less than or equal to M and M is from 1 to 255 characters.