-
Every table is composed of a number of columns.
-
For each column, an intended data type must be specified.
-
A data type is essentially a constraint on a column which defines and limits the type of values that can be stored in that column.
MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types.
MySQL supports all of the standard SQL numeric data types. These types include the exact numeric data types
(INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).
The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.
| Type |
Bytes |
Minimum Value |
Maximum Value |
| |
|
(Signed/Unsigned) |
(Signed/Unsigned) |
| TINYINT |
1 |
-128 |
127 |
| |
|
0 |
255 |
| SMALLINT |
2 |
-32768 |
32767 |
| |
|
0 |
65535 |
| MEDIUMINT |
3 |
-8388608 |
8388607 |
| |
|
0 |
16777215 |
| INT |
4 |
-2147483648 |
2147483647 |
| |
|
0 |
4294967295 |
| BIGINT |
8 |
-9223372036854775808 |
9223372036854775807 |
| |
|
0 |
18446744073709551615 |
Storage Requirements for Numeric Types in MyISAM
| Data Type |
Storage Required |
| TINYINT |
1 byte |
| SMALLINT |
2 bytes |
| MEDIUMINT |
3 bytes |
| INT, INTEGER |
4 bytes |
| BIGINT |
8 bytes |
| FLOAT(p) |
4 bytes if 0 <= p <= 24, 8 bytes if 25 <= p <= 53 |
| FLOAT |
4 bytes |
| DOUBLE [PRECISION], REAL |
8 bytes |
| DECIMAL(M,D), NUMERIC(M,D) |
Varies |
| BIT(M) |
approximately (M+7)/8 bytes |
Storage Requirements for Date and Time Types in MyISAM
The date/time data types store temporal values such as dates and times.
| Data Type |
Storage Required |
Format |
Range |
| DATE |
3 bytes |
YYYY-MM-DD |
1000-01-01 through 9999 |
| TIME |
3 bytes |
HH:MM:SS |
-838:59:59 to 838:59:59 |
| DATETIME |
8 bytes |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 through 9999 |
| TIMESTAMP |
4 bytes |
YYYY-MM-DD HH:MM:SS |
1970-01-01 00:00:00 to partway through 2037 |
| YEAR |
1 byte |
YYYY |
1901 to 2155 (and 0000) |
Storage Requirements for String Types in MyISAM
| Data Type |
Storage Required, Description |
| CHAR(M) |
M × w bytes, 0 <= M <= 255, where w is the number of bytes required for the maximum-length character in the character set |
| BINARY(M) |
M bytes, 0 <= M <= 255 |
| VARCHAR(M), VARBINARY(M) |
L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes |
| TINYBLOB, TINYTEXT |
L + 1 bytes, where L < 28 [Max Size: 255 characters (355 bytes)] |
| BLOB, TEXT |
L + 2 bytes, where L < 216 [Max Size: 65,535 characters (64 KB)] |
| MEDIUMBLOB, MEDIUMTEXT |
L + 3 bytes, where L < 224 [Max Size: 16,777,215 characters (16 MB)] |
| LONGBLOB, LONGTEXT |
L + 4 bytes, where L < 232 [Max Size: 4,294,967,295 characters (4 GB)] |
| ENUM(‘value1′,’value2’,…) |
1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum) |
| SET(‘value1′,’value2’,…) |
1, 2, 3, 4, or 8 |
- Binary data types are designed to store large amounts of data, such as pictures and varying messages.
- The main difference between Text data and Binary data types is that text data types are associated with a specific character set. Binary columns are treated as strings, and sorting is case sensitive.
- Text columns are treated according to their character sets, and sorting is based on the collation for that character set. The advantage here is that we can use a character set / collation for the column that differs from the table, database or server.
Working with NULL Values
- The NULL value can be surprising until you get used to it.
- Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.
- To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>. To demonstrate this for yourself, try the following query:
Clearly you get no meaningful results from these comparisons. Use the IS NULL and IS NOT NULL operators instead.
Comment in MySQL
MySQL Server supports three comment styles:
- From a “#” character to the end of the line.
- From a “–” sequence to the end of the line. In MySQL, the “–” (double-dash) comment style requires the second dash to be followed by at least one whitespace or control character (such as a space, tab, newline, and so on).
- From a /* sequence to the following */ sequence, as in the C programming language. This syntax enables a comment to extend over multiple lines because the beginning and closing sequences need not be on the same line.
The following example demonstrates all three comment styles:
mysql> SELECT 4+5; # This comment continues to the end of line
mysql> SELECT 4+5; -- This comment continues to the end of line
mysql> SELECT 4 /* this is an in-line comment */ + 5;
mysql> SELECT 4+
/*
this is a
multiple-line comment
*/
5;
mysql>
Comments in SQL
The standard SQL comment is two hyphens (–). However, some databases use other forms of comments as shown in the table below.
|
— |
/**/ |
# |
| Example |
–Comment |
/**/Comment |
# |
| ANSI |
YES |
NO |
NO |
| SQL Server |
YES |
YES |
NO |
| Oracle |
YES |
YES |
NO |
| MySQL |
YES |
YES |
YES |
SET and ENUM Types
The ENUM type restricts a column to a given list of values. A SET type is similar to an ENUM type, but SET type allows multiple values to be stored in the column from the list.
ALTER TABLE Passenger
ADD status ENUM('Confirmed', 'Waiting'),
ADD ticket_Details SET('PNR', 'SEATNO', 'SOURCE','DESTINATION'),
Recent Comments