2009-05-19--05-20 MySQL学习笔记02

1.整数类型

TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT或INTEGER 4字节
BIGINT 8字节

整数类型后面圆括号中的数字代表该整型字段的显示宽度，如果数字位不足就自动用空格填充，但这不会影响该字段的大小和可存储的值的范围。
UNSIGNED修饰符规定字段只保存正值；ZEROFILL修饰符规定用0来代替空格用于填补输出值，使用它的字段也不能存储负值。

[@more@]mysql> create table int_test
-> (
-> num1 tinyint,
-> num2 tinyint(3),
-> num3 tinyint unsigned,
-> num4 tinyint(3) zerofill
-> );
Query OK, 0 rows affected (0.12 sec)

mysql> desc int_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | tinyint(4) | YES | | NULL | |
| num2 | tinyint(3) | YES | | NULL | |
| num3 | tinyint(3) unsigned | YES | | NULL | |
| num4 | tinyint(3) unsigned zerofill | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
4 rows in set (0.03 sec)

mysql> insert into int_test values(1,1,1,1);
Query OK, 1 row affected (0.43 sec)

mysql> insert into int_test values(-1,-1,-1,-1);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into int_test values(-1,-1,1,-1);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into int_test values(127,127,127,127);
Query OK, 1 row affected (0.59 sec)

mysql> insert into int_test values(128,128,128,128);
ERROR 1264 (22003): Out of range value for column 'num1' at row 1
mysql> insert into int_test values(127,127,128,127);
Query OK, 1 row affected (0.04 sec)

mysql> select * from int_test;
+------+------+------+------+
| num1 | num2 | num3 | num4 |
+------+------+------+------+
| 1 | 1 | 1 | 001 |
| 127 | 127 | 127 | 127 |
| 127 | 127 | 128 | 127 |
+------+------+------+------+
3 rows in set (0.03 sec)


2.浮点类型

FLOAT 4字节
DOUBLE或REAL DOUBLE PRECISION 8字节
DECIMAL或DEC NUMERIC 对DECIMAL(M,D)，如果M>D，为M+2，否则为D+2。

浮点类型后面圆括号中的两个数字分别为一个显示宽度指示器和一个小数点指示器。
UNSIGNED和ZEROFILL修饰符同样可以用于浮点类型，效果与用于整数类型时类似。
MySQL以二进制格式保存DECIMAL数据类型，用于保存必须为确切精度的值。

mysql> create table float_test
-> (
-> num1 float,
-> num2 float(5,2),
-> num3 float unsigned,
-> num4 float(5,2) zerofill,
-> num5 decimal,
-> num6 decimal(5,2)
-> );
Query OK, 0 rows affected (0.58 sec)

mysql> desc float_test;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| num1 | float | YES | | NULL | |
| num2 | float(5,2) | YES | | NULL | |
| num3 | float unsigned | YES | | NULL | |
| num4 | float(5,2) unsigned zerofill | YES | | NULL | |
| num5 | decimal(10,0) | YES | | NULL | |
| num6 | decimal(5,2) | YES | | NULL | |
+-------+------------------------------+------+-----+---------+-------+
6 rows in set (0.05 sec)

mysql> insert into float_test values (123.321, 123.321, 123.321, 123.321, 123.32
1, 123.321);
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> insert into float_test values (-123.3, -123.3, -123.3, -123.3, -123.3, -1
23.3);
ERROR 1264 (22003): Out of range value for column 'num3' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, -123.3, -123.3, -12
3.3);
ERROR 1264 (22003): Out of range value for column 'num4' at row 1
mysql> insert into float_test values (-123.3, -123.3, 123.3, 123.3, -123.3, -123
.3);
Query OK, 1 row affected, 1 warning (0.04 sec)

mysql> insert into float_test values (123.456, 123.456, 123.456, 123.456, 123.45
6, 123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> insert into float_test values (-123.456, -123.456, 123.456, 123.456, -123
.456, -123.456);
Query OK, 1 row affected, 2 warnings (0.03 sec)

mysql> select * from float_test;
+----------+---------+---------+--------+------+---------+
| num1 | num2 | num3 | num4 | num5 | num6 |
+----------+---------+---------+--------+------+---------+
| 123.321 | 123.32 | 123.321 | 123.32 | 123 | 123.32 |
| -123.3 | -123.30 | 123.3 | 123.30 | -123 | -123.30 |
| 123.456 | 123.46 | 123.456 | 123.46 | 123 | 123.46 |
| -123.456 | -123.46 | 123.456 | 123.46 | -123 | -123.46 |
+----------+---------+---------+--------+------+---------+
4 rows in set (0.02 sec)


3.字符串类型

CHAR 0-255字节
VARCHAR 0-255字节
TINYBLOB 0-255字节
TINYTEXT 0-255字节
BLOB 0-65535字节
TEXT 0-65535字节
MEDIUMBLOB 0-16777215字节
MEDIUMTEXT 0-16777215字节
LONGBLOB 0-294967295字节
LONGTEXT 0-294967295字节

CHAR类型用于定长字符串，其后面圆括号中的数字指定要存储的值的长度，范围为0到255。比指定长度小的值会用空格适当填补，比指定长度大的值将被自动截短。
VARCHAR类型用于变长字符串，其后面圆括号中的数字指定存储的值的最大长度（必须指定），范围为0到255。比指定最大长度小的值会以其实际大小存储，不会用空格填充，比指定最大长度大的值将被自动截短。
CHAR和VARCHAR类型的值默认情况下不区分大小写，可以使用BINARY修饰符改变这一点。

mysql> create table char_test
-> (
-> string1 char,
-> string2 char(5),
-> string3 char(5) binary,
-> string4 varchar(5),
-> string5 varchar(5) binary
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> desc char_test;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | char(1) | YES | | NULL | |
| string2 | char(5) | YES | | NULL | |
| string3 | char(5) | YES | | NULL | |
| string4 | varchar(5) | YES | | NULL | |
| string5 | varchar(5) | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
ERROR 1406 (22001): Data too long for column 'string1' at row 1

这里发生了错误，要插入的记录第一个字段的长度超过了string1字段默认指定的长度1，这是因为服务器运行在严格模式。查看my.ini中的相关设置：

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

将这部分修改为：

# Set the SQL mode to strict
#sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重启服务器，再重新尝试：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

这次记录顺利插入char_test表。继续之前的实验：

mysql> insert into char_test values ('abc', 'abc', 'abc', 'abc', 'abc');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> insert into char_test values ('abcde', 'abcde', 'abcde', 'abcde', 'abcde'
);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> select * from char_test;
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
| a | abcde | abcde | abcde | abcde |
+---------+---------+---------+---------+---------+
2 rows in set (0.00 sec)

mysql> select * from char_test where string2 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.41 sec)

mysql> select * from char_test where string3 = 'ABC';
Empty set (0.00 sec)

mysql> select * from char_test where string4 = 'ABC';
+---------+---------+---------+---------+---------+
| string1 | string2 | string3 | string4 | string5 |
+---------+---------+---------+---------+---------+
| a | abc | abc | abc | abc |
+---------+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from char_test where string5 = 'ABC';
Empty set (0.00 sec)


BLOB和TEXT（TEXT类型是不区分大小写的BLOB类型?）类型用于存储比较长的可变长字符串，但不能像VARCHAR类型那样为它们指定最大长度。每一种BLOG或TEXT类型的最大长度都是固定的，超过的值将被自动截短。如果不确定应该使用哪种BLOB或TEXT类型，可以在BLOB或TEXT（其它的不可以）后面加一个圆括号，并在括号中输入字符串的最大长度，这样系统会自动选择合适的类型。

mysql> create table blob_text_test
-> (
-> string1 blob(255),
-> string2 text(255),
-> string3 blob(65535),
-> string4 text(65535),
-> string5 blob(16777215),
-> string6 text(16777215),
-> string7 blob(294967295),
-> string8 text(294967295)
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc blob_text_test
-> ;
+---------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| string1 | tinyblob | YES | | NULL | |
| string2 | tinytext | YES | | NULL | |
| string3 | blob | YES | | NULL | |
| string4 | text | YES | | NULL | |
| string5 | mediumblob | YES | | NULL | |
| string6 | mediumtext | YES | | NULL | |
| string7 | longblob | YES | | NULL | |
| string8 | longtext | YES | | NULL | |
+---------+------------+------+-----+---------+-------+
8 rows in set (0.03 sec)


4.日期和时间类型

类型 大小 范围 格式 用途
DATE 3 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值
TIME 3 '-838:59:59' ~ '838:59:59' HH:MM:SS 时间值或持续时间
YEAR 1 1901 ~ 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00 ~ 2037年的某个时候 YYYYMMDDHHMMSS 混合日期和时间，时间戳

这些类型可以描述为字符串或不带分隔符的整数序列，如果描述为字符串，应该按照上表“范围”列中的格式。

mysql> create table date_time_test
-> (
-> dt1 date,
-> dt2 time,
-> dt3 year,
-> dt4 datetime,
-> dt5 timestamp
-> );
Query OK, 0 rows affected (0.49 sec)

mysql> insert into date_time_test values ('2008-05-12', '014:28:57', '2008', '20
08-05-12 14:28:57', '2008-05-12 14:28:57');
Query OK, 1 row affected (0.03 sec)

mysql> insert into date_time_test values (20080512, 0142857, 2008, 2008051214285
7, 20080512142857);
Query OK, 1 row affected (0.03 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
+------------+----------+------+---------------------+---------------------+
2 rows in set (0.00 sec)

可以看到，使用字符串和整数序列描述方式插入的记录的是完全相同的。当输入的值格式不标准时，MySQL尽可能地去“理解”它们：

mysql> insert into date_time_test values ('2008-5-12', '3:4:5', '08', '2008-5-12
3:4:5', '08-5-12 3:4:5');
Query OK, 1 row affected (0.04 sec)

mysql> insert into date_time_test values (080512, 142857, 008, 2008512345, 08051
21428);
Query OK, 1 row affected, 1 warning (0.44 sec)

mysql> insert into date_time_test values ('2008-05', 1428, '8', '8-5-12 14:28',
'2008-05 14:28');
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> select * from date_time_test;
+------------+----------+------+---------------------+---------------------+
| dt1 | dt2 | dt3 | dt4 | dt5 |
+------------+----------+------+---------------------+---------------------+
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 14:28:57 | 2008 | 2008-05-12 14:28:57 | 2008-05-12 14:28:57 |
| 2008-05-12 | 03:04:05 | 2008 | 2008-05-12 03:04:05 | 2008-05-12 03:04:05 |
| 2008-05-12 | 14:28:57 | 2008 | 0000-00-00 00:00:00 | 2000-08-05 12:14:28 |
| 0000-00-00 | 00:14:28 | 2008 | 0008-05-12 14:28:00 | 0000-00-00 00:00:00 |
+------------+----------+------+---------------------+---------------------+
5 rows in set (0.00 sec)

对于TIMESTAMP类型，当被指定为NULL，或作为记录中的第一个字段而未被明确指定值时，MySQL将会用当前的日期和时间自动填充它，而DATETIME类型则不行。

mysql> create table ts_dt_test
-> (
-> dt1 timestamp,
-> dt2 timestamp,
-> dt3 datetime
-> );
Query OK, 0 rows affected (0.52 sec)

mysql> desc ts_dt_test;
+-------+-----------+------+-----+---------------------+------------------------
-----+
| Field | Type | Null | Key | Default | Extra
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
| dt1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMES
TAMP |
| dt2 | timestamp | NO | | 0000-00-00 00:00:00 |
|
| dt3 | datetime | YES | | NULL |
|
+-------+-----------+------+-----+---------------------+------------------------
-----+
3 rows in set (0.01 sec)


mysql> insert into ts_dt_test values (NULL, NULL, NULL);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ts_dt_test (dt3) values (NOW());
Query OK, 1 row affected (0.06 sec)

mysql> insert into ts_dt_test (dt2, dt3) values (NOW(), NOW());
Query OK, 1 row affected (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
+---------------------+---------------------+---------------------+
3 rows in set (0.00 sec)

最后，尝试插入不合法的日期和时间值，MySQL会自动将其置0。

mysql> insert into ts_dt_test values ('2009-02-30', '2009-05-32', '2009-01-01 24
:00:00');
Query OK, 1 row affected, 3 warnings (0.03 sec)

mysql> select * from ts_dt_test;
+---------------------+---------------------+---------------------+
| dt1 | dt2 | dt3 |
+---------------------+---------------------+---------------------+
| 2009-05-20 15:09:20 | 2009-05-20 15:09:20 | NULL |
| 2009-05-20 15:10:20 | 0000-00-00 00:00:00 | 2009-05-20 15:10:20 |
| 2009-05-20 15:10:59 | 2009-05-20 15:10:59 | 2009-05-20 15:10:59 |
| 2009-05-20 15:26:44 | 2009-05-20 15:20:11 | NULL |
| 0000-00-00 00:00:00 | 0000-00-00 00:00:00 | 0000-00-00 00:00:00 |
+---------------------+---------------------+---------------------+
5 rows in set (0.00 sec)


5.复合类型

MySQL有两个复合类型ENUM和SET，它们扩展了SQL的规范。ENUM类型必须从一个允许值的集合中选择单个值，而SET类型可以从允许值的集合中选择任意多个值。详细用法这里不再赘述。