Mysql数据类型
Mysql提供了多种数据类型，主要有数值型、字符串类型、日期和时间类型。不同的Mysql版本可能支持的数据类型可能会不同。
create database mysqlstu charset=utf8;
grant all privileges on mysqlstu.* to chengdu@localhost;
数值类型
数值类型分为整数类型、浮点数类型、定点数类型、位类型。对于整数类型数据，MySQL支持在类型名称后面的小括号内指定显示宽度，如果不显示指定宽度则默认为int(11)。
整数类型
mysql> use mysqlstu;
Database changed
mysql> show tables;
Empty set (0.01 sec)
mysql> create table intdata (
-> field1 tinyint,
-> field2 smallint,
-> field3 mediumint,
-> field4 int,
-> field5 bigint ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.18 sec)
mysql> desc intdata;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| field1 | tinyint(4) | YES | | NULL | |
| field2 | smallint(6) | YES | | NULL | |
| field3 | mediumint(9) | YES | | NULL | |
| field4 | int(11) | YES | | NULL | |
| field5 | bigint(20) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
5 rows in set (0.06 sec)
mysql> insert into intdata(field1, field2, field3, field4, field5) values(-128,
-32768, -8388608, -2147483648,-9223372036854775808);
Query OK, 1 row affected (0.12 sec)
mysql> select * from intdata;
+--------+--------+----------+-------------+----------------------+
| field1 | field2 | field3 | field4 | field5 |
+--------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
+--------+--------+----------+-------------+----------------------+
1 row in set (0.00 sec)
mysql> insert into intdata(field1, field2, field3, field4, field5) values(-129,
-32768, -8388608, -2147483648,-9223372036854775808);
ERROR 1264 (22003): Out of range value for column 'field1' at row 1
mysql> insert into intdata(field1, field2, field3, field4, field5) values(127, 3
2767, 8388607, 2147483647, 9223372036854775807);
Query OK, 1 row affected (0.06 sec)
mysql> select * from intdata;
+--------+--------+----------+-------------+----------------------+
| field1 | field2 | field3 | field4 | field5 |
+--------+--------+----------+-------------+----------------------+
| -128 | -32768 | -8388608 | -2147483648 | -9223372036854775808 |
| 127 | 32767 | 8388607 | 2147483647 | 9223372036854775807 |
+--------+--------+----------+-------------+----------------------+
2 rows in set (0.00 sec)
mysql> insert into intdata(field1, field2, field3, field4, field5) values(128, 3
2767, 8388607, 2147483647, 9223372036854775807);
ERROR 1264 (22003): Out of range value for column 'field1' at row 1
浮点数类型
mysql> create table floatdata (
-> field1 float(6,2),
-> field2 double(7,3)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql> desc floatdata;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| field1 | float(6,2) | YES | | NULL | |
| field2 | double(7,3) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
zerofill
mysql> create table zerofilltable (
-> field1 int ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> insert into zerofilltable(field1) values(2);
Query OK, 1 row affected (0.08 sec)
mysql> select * from zerofilltable;
+--------+
| field1 |
+--------+
| 2 |
+--------+
mysql> alter table zerofilltable modify field1 int zerofill;
Query OK, 1 row affected (0.22 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from zerofilltable;
+------------+
| field1 |
+------------+
| 0000000002 |
+------------+
1 row in set (0.00 sec)
日期和时间类型
MySQL中有多种数据类型可以用于表示时间
如果要用来表示年月日，通常用DATE来表示
如果要用来表示年月日时分秒，通常用DATETIME表示
如果只用来表示时分秒，通常用TIME表示
mysql> create table datetype (
-> field1 date,
-> field2 datetime,
-> field3 timestamp,
-> field4 time,
-> field5 year ) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.07 sec)
mysql> desc datetype
-> ;
+--------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------+------+-----+---------+-------+
| field1 | date | YES | | NULL | |
| field2 | datetime | YES | | NULL | |
| field3 | timestamp | YES | | NULL | |
| field4 | time | YES | | NULL | |
| field5 | year(4) | YES | | NULL | |
+--------+-----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2018-11-23 08:59:07 |
+---------------------+
1 row in set (0.00 sec)
mysql> insert into datetype(field1,field2,field3,field4,field5) values(now(),now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+-------+------+----------------------------------------------------------------
----------+
| Level | Code | Message
|
+-------+------+----------------------------------------------------------------
----------+
| Note | 1292 | Incorrect date value: '2018-11-23 09:00:20' for column 'field1'
at row 1 |
+-------+------+----------------------------------------------------------------
----------+
1 row in set (0.00 sec)
mysql> select * from datetype;
+------------+---------------------+---------------------+----------+--------+
| field1 | field2 | field3 | field4 | field5 |
+------------+---------------------+---------------------+----------+--------+
| 2018-11-23 | 2018-11-23 09:00:20 | 2018-11-23 09:00:20 | 09:00:20 | 2018 |
+------------+---------------------+---------------------+----------+--------+
1 row in set (0.00 sec)
TIMESTAMP 和时区相关，当插入日期的时，会先转换为本地时区后存放，而从数据库里面取出来时候，也需要将日期转换为本地时区后显示。
mysql> show warnings;
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, b
ut will be an alias for UTF8MB4 in a future release. Please consider using UTF8M
B4 in order to be unambiguous. |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)
mysql> create table timestamptest (
-> field1 timestamp default current_timestamp,
-> field2 datetime ) charset=utf8mb4;
Query OK, 0 rows affected (0.08 sec)
mysql> desc timestamptest;
+--------+-----------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------+------+-----+-------------------+-------+
| field1 | timestamp | YES | | CURRENT_TIMESTAMP | |
| field2 | datetime | YES | | NULL | |
+--------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)
mysql> insert into timestamptest(field1, field2) values(now(), now());
Query OK, 1 row affected (0.10 sec)
mysql> select * from timestamptest;
+---------------------+---------------------+
| field1 | field2 |
+---------------------+---------------------+
| 2018-11-23 09:35:29 | 2018-11-23 09:35:29 |
+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | SYSTEM |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)
mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'time_zone';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| time_zone | +09:00 |
+---------------+--------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from timestamptest;
+---------------------+---------------------+
| field1 | field2 |
+---------------------+---------------------+
| 2018-11-23 10:35:29 | 2018-11-23 09:35:29 |
+---------------------+---------------------+
1 row in set (0.00 sec)
TIMESTAMP支持的时间范围比较小， DATETIME支持的时间范围大
TIMESTAMP 插入和查询都受当地的时区影响，DATETIME 只能反映出插入时当地的时区
字符串类型
MySQL中提供了多种对字符数据的存储类型，不同版本可能有所差异。字符串类型有char、varchar、binary、varbinary、blob、text、enum、set等多种字符串类型。
CHAR和CARCHAR类型
char和varchar用来保存比较短的字符串。char固定长度，varchar列中的值为可变长字符串
mysql> create table cv(
-> field1 char(5),
-> field2 varchar(5)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> insert into cv(field1, field2) values('1234 ','1234 ');
Query OK, 1 row affected (0.04 sec)
mysql> select * from cv;
+--------+--------+
| field1 | field2 |
+--------+--------+
| 1234 | 1234 |
+--------+--------+
1 row in set (0.00 sec)
mysql> select length(field1), length(field2) from cv;
+----------------+----------------+
| length(field1) | length(field2) |
+----------------+----------------+
| 4 | 5 |
+----------------+----------------+
1 row in set (0.00 sec)
mysql> insert into cv(field1, field2) values('abcdef','abcdef');
ERROR 1406 (22001): Data too long for column 'field1' at row 1
mysql> insert into cv(field1, field2) values('abcde','abcdef');
ERROR 1406 (22001): Data too long for column 'field2' at row 1
mysql> insert into cv(field1, field2) values('abcde','abcde');
Query OK, 1 row affected (0.09 sec)
在检索的时候，char列删除了尾部的空格，而varchar则保留了这些空格。
char、varchar需要指定长度
BINARY 和 VARBINARY
binary和varbinary包含二进制字符串而不包含非二进制字符串
mysql> create table binarytest (
-> field1 binary(5),
-> field2 varbinary(5)) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> insert into binarytest(field1, field2) values('1','1');
Query OK, 1 row affected (0.03 sec)
mysql> select *,field1='1',field1='1\0\0\0\0' from binarytest;
+--------+--------+------------+--------------------+
| field1 | field2 | field1='1' | field1='1\0\0\0\0' |
+--------+--------+------------+--------------------+
| 1 | 1 | 0 | 1 |
+--------+--------+------------+--------------------+
1 row in set (0.00 sec)
当保存BINARY值时，在值的最后面填充"0x00"(零字节)达到指定的字段定义长度。
ENUM 类型
mysql> create table enumtest (
-> language enum('Java', 'Python', 'C++', 'C')) charset=utf8;
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Level | Code | Message
|
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, b
ut will be an alias for UTF8MB4 in a future release. Please consider using UTF8M
B4 in order to be unambiguous. |
+---------+------+--------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)
mysql> insert into enumtest(language) values('1'),('3'),(2);
Query OK, 3 rows affected (0.11 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from enumtest;
+----------+
| language |
+----------+
| Java |
| C++ |
| Python |
+----------+
3 rows in set (0.00 sec)
mysql> desc enumtest;
+----------+---------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------------------+------+-----+---------+-------+
| language | enum('Java','Python','C++','C') | YES | | NULL | |
+----------+---------------------------------+------+-----+---------+-------+
set类型
set是一个集合，类似于Java语言集合，mysql用set作为字段时，该数据字段无重复元素
mysql> create table settest (
-> filed1 set('a','b','c','d') ) charset=utf8mb4;
Query OK, 0 rows affected (0.10 sec)
mysql> insert into settest(field1) values('a,e');
ERROR 1265 (01000): Data truncated for column 'field1' at row 1
mysql> insert into settest(field1) values('a,b');
Query OK, 1 row affected (0.06 sec)
mysql> insert into settest(field1) values('a,b'),('a,b,c,a');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from settest;
+--------+
| field1 |
+--------+
| a,b |
| a,b |
| a,b,c |
+--------+
3 rows in set (0.00 sec)
在建表的时候，可以根据相应的特点来选择合适的数据类型，了解更详细内容阅读书籍《深入浅出MySQL++数据库开发、优化与管理维护》
3723
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
