2009-05-19--05-20 MySQL学习笔记02
发表于：2026-11-03 作者：千家信息网编辑
千家信息网最后更新 2026年11月03日，1.整数类型TINYINT 1字节SMALLINT 2字节MEDIUMINT 3字节INT或INTEGER 4字节BIGINT 8字节整数类型后面圆括号中的数字代表该整型字段的显示宽度，如果数字位不足
千家信息网最后更新 2026年11月03日2009-05-19--05-20 MySQL学习笔记02
-> (
-> 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类型可以从允许值的集合中选择任意多个值。详细用法这里不再赘述。
1.整数类型
TINYINT 1字节
SMALLINT 2字节
MEDIUMINT 3字节
INT或INTEGER 4字节
BIGINT 8字节
整数类型后面圆括号中的数字代表该整型字段的显示宽度，如果数字位不足就自动用空格填充，但这不会影响该字段的大小和可存储的值的范围。
UNSIGNED修饰符规定字段只保存正值；ZEROFILL修饰符规定用0来代替空格用于填补输出值，使用它的字段也不能存储负值。
-> (
-> 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类型可以从允许值的集合中选择任意多个值。详细用法这里不再赘述。
类型
字节
长度
字符
字符串
时间
字段
最大
日期
存储
圆括号
大小
数字
整数
范围
格式
空格
浮点
选择
两个
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
广州万易互联网科技有限公司
西安纸贵互联网科技有限公司
互联网和科技创新
互联网的科技创新
安徽鑫晟辉互联网科技
未来的互联网科技
云电互联网科技
滨湖区互联网智能科技产品供应
海南购全球互联网科技
新兴科技互联网创新大赛颁奖典礼
成都互联网科技公司工作好不好
华夏恒生科技与互联网科技
互联网科技创新的路径
互联网数据科技专业
张召忠谈中国互联网科技
江苏互联网智能科技产品出厂价
艺电能源互联网科技有限公司招聘
互联网科技收购价值
重庆科技产业互联网有哪些
网互联网络科技
广东猪兼强互联网科技有限公司
互联网和科技技术
上海挚品互联网科技
百单互联网科技有限公司
广州凡科互联网科技
互联网科技中介
互联网科技与管理
天津腾达互联网科技有限公司
互联网科技和法律行业
互联网科技公司营业执照怎么办理
