Exploring the Various Column Types
|
|
To use MySQL effectively it's important to understand the basic building blocks available to you. The MySQL mailing lists are full of cries for help where the solution is often as simple as using another column or table type or having a better understanding of its features. This chapter first discusses the various column types, and then goes on to look at the table types available to MySQL.
There are three main types of columns in MySQL: numeric, string, and date. Although there are many more specific types, which you'll learn about shortly, you can classify each of these into one of the three main types. Generally, you should choose the smallest possible column type, as this will save space and be faster to access and update. However, choosing too small a column can result in data being lost or cut off when you insert it, so be sure to choose a type that covers all eventualities. The following sections explore each type in detail.
| Note | Column names are always case insensitive, so SELECT field1 FROM tablename is the same as SELECT FieLD1 FROM tablename. Note, however, that table and database names can be case sensitive! By default they are not case sensitive on Windows, but they are case sensitive on most versions of Unix, except MacOS X. |
Numeric Column Types
Numeric types are designed for storing any kind of numeric data, such as prices, ages, or quantities. There are two main kinds of numeric types: integer types (whole numbers, without any decimal places or fractional parts) and floating-point types. All numeric types allow two options: UNSIGNED and ZEROFILL. UNSIGNED prohibits negative numbers (extending the positive range of the type for integer types), and ZEROFILL pads the value with zeroes instead of the usual spaces, as well as automatically making it UNSIGNED. For example:
mysql> CREATE TABLE test1(id TINYINT ZEROFILL); Query OK, 0 rows affected (0.32 sec) mysql> INSERT INTO test1 VALUES(3); Query OK, 1 row affected (0.16 sec) mysql> INSERT INTO test1 VALUES (-1) Query OK, 1 row affected (0.16 sec) mysql> INSERT INTO test1 VALUES (256) Query OK, 1 row affected (0.16 sec) mysql> SELECT * from test1; +------+ | id | +------+ | 003 | | 000 | | 255 | +------+ 3 rows in set (0.00 sec)
Notice that because the field is UNSIGNED, the negative number is adjusted to fit into the bottom of the range, and because the 256 exceeds the maximum of the range, it is adjusted to 255, the maximum allowable positive value.
| Note | When performing a query on a numeric column type, you do not need to use quotes around the numeric value. |
Table 2.1 lists the numeric types available in MySQL.
| Type | Description |
|---|---|
| TINYINT[(M)] [UNSIGNED] [ZEROFILL] | A tiny integer, 128 to 127 (SIGNED), 0 to 255 (UNSIGNED), 1 byte of storage required. |
| BIT | A synonym for TINYINT(1). |
| BOOL | Another synonym for TINYINT(1). |
| SMALLINT[(M)] [UNSIGNED] [ZEROFILL] | A small integer; 32,768 to 32,767 (SIGNED); 0 to 65,535 (UNSIGNED); 2bytes of storage required. |
| MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] | A medium-size integer, 8,388,608 to 8,388,607 (SIGNED); 0 to 16,777,215 (UNSIGNED); 3 bytes of storage required. |
| INT[(M)] [UNSIGNED] [ZEROFILL] | An integer; 2,147,483,648 to 2,147,483,647 (SIGNED); 0 to 4,294,967,295 (UNSIGNED); 4 bytes of storage required. |
| INTEGER | A synonym for INT. |
| BIGINT[(M)] [UNSIGNED] [ZEROFILL] | A big integer, 9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (SIGNED); 0 to 18,446,744,073,709,551,615 (UNSIGNED); 8 bytes of storage required. See the rules after this table for some important considerations when using BIGINTs. |
| FLOAT(precision) [UNSIGNED] [ZEROFILL] | A floating-point number. precision <=24 is for a single-precision floating-point number. precision between 25 and 53 is for a double-precision floating-point number. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. Prior to MySQL version 3.23, this was not a true floating-point value and always had two decimals. This may give some unexpected problems as all calculations in MySQL are done with double precision. |
| FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] | A small or single-precision floating-point number. 3.402823466E+38 to 1.175494351E38, 0, and 1.175494351E38 to 3.402823466E+38. With UNSIGNED, the positive range stays the same, but negative numbers are disallowed. M refers to the total display width, and D refers to the number of decimals. FLOAT without arguments or FLOAT(X) where X <= 24 standsfor a single-precision floating-point number. FLOAT(X) where X is between 25 and 53 stands for a double-precision floating-point number. 4bytes of storage are required (single-precision). |
| DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] | A double-precision floating-point number. 1.7976931348623157E+308 to2.2250738585072014E308, 0, and 2.2250738585072014E308 to 1.7976931348623157E+308. As with FLOAT, UNSIGNED will leave the positive range untouched, but disallow negative numbers. M refers to the total display width and D to the number of decimals. DOUBLE without arguments or FLOAT(X) where 25 <= X <= 53 stands for a double-precision floating-point number. 8 bytes of storage are required. |
| DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] | A synonym for DOUBLE. |
| REAL[(M,D)] [UNSIGNED] [ZEROFILL] | Another synonym for DOUBLE. |
| DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] | A decimal number is stored like a string, one byte for each character (thisiscalled unpackedall other numeric types are packed). From 1.7976931348623157E+308 to 2.2250738585072014E308, 0, and 2.2250738585072014E308 to 1.7976931348623157E+308. M refers to the total number of digits (excluding the sign and decimal point, except for versions earlier than 3.23). D refers to the number of digits after the decimal point. It should always be less than M. D is by default 0 if omitted. Unlike other numeric types, M and D can constrain the range of allowed values. With UNSIGNED, negative values are disallowed. |
| DEC[(M[,D])] [UNSIGNED] [ZEROFILL] | A synonym for DECIMAL. |
| NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] | Another synonym for DECIMAL. |
Use the following guidelines when deciding what numeric type to choose:
-
Choose the smallest applicable type (TINYINT rather than INT if the value would never go beyond 127 signed).
-
For whole numbers, choose an integer type. (Remember that money can also be stored as a whole number—for example, it can be stored in cents rather than dollars, which are integers.) It could also be reasonably stored as a DECIMAL.
-
For high precision, use integer types rather than floating-point types (rounding errors afflict floating-point numbers).
The M value in Table 2.1 often causes confusion. Setting M to a higher value than the type allows will not allow you to extend its limit. For example:
mysql> CREATE TABLE test2(id TINYINT(10)); Query OK, 0 rows affected (0.32 sec) mysql> INSERT INTO test2(id) VALUES(100000000); Query OK, 1 row affected (0.00 sec) mysql> SELECT id FROM test2; +------+ | id | +------+ | 127 | +------+ 1 row in set (0.00 sec)
Even though the figure inserted was fewer than 10 digits, because it is a signed TINYINT, it is limited to a maximum positive value of 127.
This optional width specification left-pads the display of values whose width is less than the width specified for the column, but, with the exception of DECIMAL fields, it does not constrain the range of values that can be stored in the column or the number of digits that will be displayed for values whose width exceeds that specified for the column.
However, if you try to limit a type to less than its allowable limit, the value is not cut off. It neither constrains the range that can be stored nor the number of digits displayed. For example:
mysql> CREATE TABLE test3(id INT(1)); Query OK, 0 rows affected (0.32 sec) mysql> INSERT INTO test3(id) VALUES(42432432); Query OK, 1 row affected (0.00 sec) mysql> SELECT id FROM test3; +----------+ | id | +----------+ | 42432432 | +----------+ 1 row in set (0.16 sec)
The width specification is most often used with zerofill because you can easily see the results:
mysql> CREATE TABLE test4(id INT(3) ZEROFILL,id2 INT ZEROFILL); Query OK, 0 rows affected (0.32 sec) mysql> INSERT INTO test4(id,id2) VALUES (22,22); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test4; +------+------------+ | id | id2 | +------+------------+ | 022 | 0000000022 | +------+------------+ 1 row in set (0.22 sec)
The effect of the width specification in id is to limit the display to three characters, while the id2 field uses the normal unsigned INT default (10).
String Column Types
String columns are for storing any kind of character data, such as names, addresses, or newspaper articles. Table 2.2 describes the string types available to MySQL.
| Type | Description |
|---|---|
| [NATIONAL] CHAR(M) [BINARY] | Character. A fixed-length string, right-padded with spaces to the specified length. From 0 to 255 characters (1 to 255 prior to MySQL version 3.23). Trailing spaces are removed when the value is retrieved. |
| CHAR | This is a synonym for CHAR(1). |
| [NATIONAL] VARCHAR(M) [BINARY] | Variable-length character. A variable-length string, where trailing spaces are removed when the value is stored (this is a bug, and can catch out those coming from another DBMS, where this is not the case). From 0 to 255 characters (1 to 255 prior to MySQL version 4.0.2). |
| TINYBLOB | Tiny binary large object. Maximum 255 characters (28- 1). Requires length + 1 bytes storage. Same as TINYTEXT, except that searching is done case sensitively. In most situations, use rather use VARCHAR BINARY, as it should be faster. |
| TINYTEXT | Maximum 255 characters (28- 1). Requires length + 1 bytes storage. Same as TINYBLOB, except that searching is done case insensitively. In most situations, rather use VARCHAR, as it should be faster. |
| BLOB | Binary large object. Maximum 65,535 characters (216- 1). Requires length + 2 bytes storage. Same as TEXT, except that searching is done case sensitively. |
| TEXT | Maximum 65,535 characters (216- 1). Requires length + 2 bytes storage. Same as BLOB, except that searching is done case insensitively. |
| MEDIUMBLOB | Medium-sized binary large object. Maximum 16,777,215 characters (224- 1). Requires length + 3 bytes storage. Same as MEDIUMTEXT, except that searching is done case sensitively. |
| MEDIUMTEXT | Maximum 16,777,215 characters (224- 1). Requires length + 3 bytes storage. Same as MEDIUMBLOB, except that searching is done case insensitively. |
| LONGBLOB | Large binary large object. Maximum of 4,294,967,295 characters (232- 1). Requires length + 4 bytes storage. Same as LONGTEXT, except that searching is done case sensitively. Note that because of external limitations, there is a limit of 16MB per communication packet/table row. |
| LONGTEXT | Maximum of 4,294,967,295 characters (232- 1). Requires length + 4 bytes storage. Same as LONGBLOB, except that searching is done case insensitively. Note that because of external limitations, there is a limit of 16MB per communication packet/table row. |
| ENUM('value1','value2',...) | Enumeration. Can only have one of the specified values, NULL or "". Maximum of 65,535 values. |
| SET('value1','value2',...) | A set. Can contain zero to 64 values from the specified list. |
Use the following guidelines when deciding what string type to choose:
-
Never store numbers in string columns. It is much more efficient to store them in numeric columns. Each digit in a string field takes up an entire byte, as opposed to a numeric field, which is stored in bits. Also, ordering numbers if they are stored as a string may yield inconsistent results.
-
For speed, choose fixed columns, such as CHAR.
-
To save space, use dynamic columns, such as VARCHAR.
-
For limiting contents of a column to one choice, use ENUM.
-
For allowing more than one entry in a column, choose SET.
-
For text you want to search case insensitively, use TEXT.
-
For text you want to search case sensitively, use BLOB.
-
For images, and other binary objects, store them on the file system rather than directly in the database.
By default, CHAR and VARCHAR types are searched case insensitively, unless you use the BINARY keyword. For example:
mysql> CREATE TABLE test5(first_name CHAR(10)); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO test5(first_name) VALUES ('Nkosi'); Query OK, 1 row affected (0.06 sec) mysql> SELECT first_name FROM test5 WHERE first_name='nkosi'; +------------+ | first_name | +------------+ | Nkosi | +------------+ 1 row in set (0.17 sec) This search returned a result even though you specified nkosi rather than Nkosi. If you ALTER the table, specifying the first_name column as BINARY, you do not find the result, as follows:
mysql> ALTER TABLE test5 CHANGE first_name first_name CHAR(10) BINARY; Query OK, 1 row affected (0.16 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT first_name FROM test5 WHERE first_name='nkosi'; Empty set (0.17 sec)
| Note | Searching CHAR and VARCHAR fields case insensitively is not common among most DBMSs, so be careful if you're moving to MySQL from another DBMS. |
The NATIONAL keyword is only there for ANSI SQL compliance. (ANSI stands for the American National Standards Institute, and they have developed a "standard" SQL. Most database management systems—DBMSs—adhere to this to some degree, but few do so entirely, and many have their own additions.) It tells the DBMS to use the MySQL default character set (which is the MySQL standard anyway).
| Note | Using CHAR as opposed to VARCHAR leads to larger tables, but usually faster processing, because MySQL knows exactly where each record starts. See a full discussion of this in the "MyISAM Tables" section. |
ENUM columns have some interesting features. If you add an invalid value, an empty string ("") is inserted instead, as follows:
mysql> CREATE TABLE test6(bool ENUM("true","false")); Query OK, 0 rows affected (0.17 sec) mysql> INSERT INTO test6(bool) VALUES ('true'); Query OK, 1 row affected (0.17 sec) mysql> INSERT INTO test6(bool) VALUES ('troo'); Query OK, 1 row affected (0.06 sec) mysql> SELECT bool from test6; +------+ | bool | +------+ | true | | | +------+ 2 rows in set (0.11 sec) You can also perform queries on enumerated fields based on their indexes (the first value starts at 1). In the previous example, true would reflect as an index of 1, false as an index of 2, NULL as an index of NULL, and any other value ("") as an index of 0. For example:
mysql> SELECT * FROM test6 WHERE bool=0; +------+ | bool | +------+ | | +------+ 1 row in set (0.17 sec) mysql> SELECT * FROM test6 WHERE bool=1; +------+ | bool | +------+ | true | +------+ 1 row in set (0.16 sec)
The following example shows the query. If you insert an index directly, you'll see the full enumerated value when you return a result:
mysql> INSERT INTO test6(bool) VALUES(2); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test6; +-------+ | bool | +-------+ | true | | | | false | +-------+ 3 rows in set (0.16 sec)
| Warning | LOAD DATA does not allow you to add records to an enumerated field using the index because it treats all inputs as strings. |
Enumerated fields are sorted on their index values, not alphabetically. In other words, they are sorted in the order the values were defined:
mysql> SELECT * FROM test6 ORDER BY bool ASC; +-------+ | bool | +-------+ | | | true | | false | +-------+ 3 rows in set (0.22 sec)
Sets work in a similar way to enumerated fields:
mysql> CREATE TABLE test7 (fruit SET('apple','mango','litchi','banana')); Query OK, 0 rows affected (0.11 sec) mysql> INSERT INTO test7 VALUES('banana'); Query OK, 1 row affected (0.17 sec) mysql> INSERT INTO test7 VALUES('litchi'); Query OK, 1 row affected (0.05 sec) mysql> INSERT INTO test7 VALUES('paw-paw'); Query OK, 1 row affected (0.00 sec) The difference of a SET type is that you can add multiple instances:
mysql> INSERT INTO test7 VALUES('apple,mango'); Query OK, 1 row affected (0.06 sec) mysql> SELECT * FROM test7; +-------------+ | fruit | +-------------+ | banana | | litchi | | | | apple,mango | +-------------+ 4 rows in set (0.17 sec) As with enumerations, sorting is by the index:
mysql> INSERT INTO test7 VALUES('mango,apple'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test7 ORDER BY fruit; +-------------+ | fruit | +-------------+ | | | apple,mango | | apple,mango | | litchi | | banana | +-------------+ 5 rows in set (0.11 sec) Note that the order of the elements is always the same as they were specified by the CREATE TABLE statement, so mango,apple is stored as apple,mango and appears this way in the sorted results, too.
| Note | You can create a column of type CHAR(0). This may seem useless, but it can be useful when using old applications that depend on the existence of a field but don't actually store anything in it. You can also use it if you need a field that contains only two values, NULL and "". |
Date and Time Column Types
Date and time column types are designed for the special conditions required for working with temporal data, and can be used to store data such as time of day or date of birth. Table 2.3 describes the date column types available to MySQL.
| Type | Description |
|---|---|
| DATETIME | YYYY-MM-DD HH:MM:SS from 1000-01-01 00:00:00 to 9999-12-31 23:59:59 |
| DATE | YYYY-MM-DD from 1000-01-01 to 9999-12-31 |
| TIMESTAMP | YYYYMMDDHHMMSS |
| TIME | HH:MM:SS |
| YEAR | YYYY |
The TIMESTAMP column type can be displayed in different ways, as shown in Table 2.4.
| Type | Description |
|---|---|
| TIMESTAMP(14) | YYYYMMDDHHMMSS |
| TIMESTAMP(12) | YYMMDDHHMMSS |
| TIMESTAMP(10) | YYMMDDHHMM |
| TIMESTAMP(8) | YYYYMMDD |
| TIMESTAMP(6) | YYMMDD |
| TIMESTAMP(4) | YYMM |
| TIMESTAMP(2) | YY |
This does not mean that data is lost, though. The number only affects the display; even in column defined as TIMESTAMP(2), the full 14 digits are stored, so if at a later stage you change the table definition, the full TIMESTAMP will be correctly displayed.
| Warning | Functions, except for UNIX_TIMESTAMP(), work on the display value. So the DAYOFWEEK() function will not work on a TIMESTAMP(2) or TIMESTAMP(4). |
MySQL is lenient in accepting date formats. You can replace the hyphen (–) and colon (:) characters with any other punctuation character without affecting validity. For example:
mysql> CREATE TABLE tt(ts DATETIME); mysql> INSERT INTO tt(ts) VALUES('1999+11+11 23-24'); Query OK, 1 row affected (0.06 sec) You can even replace the space with another character. The following example replaces it with an equals sign:
mysql> INSERT INTO tt(ts) VALUES('1999+12=12-12'12'); Query OK, 1 row affected (0.05 sec) If the value entered is invalid, you will not get an error message; instead, the results are set to 0 (0000 for a YEAR type, 00:00:00 for a TIME type, and so on).
|
|
