MySQL的数据类型
一、整数类型
|
整数类型 |
字节 |
取值范围 |
取值范围 |
|
tinyint |
1 |
有符号 -128 无符号 0 |
有符号 127 无符号 255 |
|
Smallint |
2 |
有符号 -32768 无符号 0 |
有符号 32767 无符号65535 |
|
mediumint |
3 |
有符号 -8388608 无符号 0 |
有符号 8388607 无符号1677215 |
|
int,integer |
4 |
有符号 -2147483648 无符号 0 |
有符号 2147483647 无符号4294967295 |
|
bigint |
8 |
有符号 -263 无符号 0 |
有符号 263-1 无符号264-1 |
tinyint、medinumint、int、bigint这个集中类型如果要是操作超出范围的话会显示"Out of range"，对于数值的类型我们一般指定数据宽度比如：int(5)，如果不指定数值的宽度则默认为int(11)。一般数值在小于指定的宽度的时候，就配合zerofill使用，就是字符空间用0填满。
创建一个数据库，里面包括id1和id2，指定数值宽度为int和int(5)，SQL语句如下
mysql> create database lianxi; Query OK, 1 row affected (0.32 sec) mysql> use lianxi Database changed mysql> create table t1(id1 int,id2 int(5)); Query OK, 0 rows affected (6.14 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | YES | | NULL | | | id2 | int(5) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.93 sec)
mysql> insert into t1(id1,id2) values(111111,111111); 插入111111数据 Query OK, 1 row affected (0.27 sec) mysql> select * from t1;------------------------------>疑问1？ +--------+--------+ | id1 | id2 | +--------+--------+ | 111111 | 111111 | +--------+--------+ 1 row in set (0.41 sec) mysql> insert into t1(id1,id2) values(1111,1111); Query OK, 1 row affected (0.04 sec) mysql> select * from t1; +--------+--------+ | id1 | id2 | +--------+--------+ | 111111 | 111111 | | 1111 | 1111 | +--------+--------+ 2 rows in set (0.00 sec) mysql> alter table t1 modify id1 int zerofill;------------------->zerofill让效果更加的明显。 Query OK, 2 rows affected (3.36 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------------+--------+ | id1 | id2 | +------------+--------+ | 0000111111 | 111111 | | 0000001111 | 1111 | +------------+--------+ 2 rows in set (0.00 sec) mysql> alter table t1 modify id2 int(5) zerofill; Query OK, 2 rows affected (0.41 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------------+--------+ | id1 | id2 | +------------+--------+ | 0000111111 | 111111 | | 0000001111 | 01111 | +------------+--------+ 2 rows in set (0.00 sec) mysql>
有很多人疑问int(5)后面的5是不是表示占用5个字节呢？那么疑问1又怎么解释呢？为什么会显示6个1呢？为什么我设置了5还会显示6个呢？
其实在int(m)中的m和你占的字节没有关系，我前面的表格中也写了int类型是4个字节，不管m是多少都是4个字节，那m是什么呢，在最后的一个select语句中我给id2添加了zerofill，是不是一目了然呢，不错，m就表示最少显示m个，为什么我输入4个1的时候没有填0呢？那是因为没有添加zerofill，但是系统在存储的时候是要添加0进去的。
对于小数MySQL有两种方式：浮点数和定点数
浮点数：double（双精度）
float（单精度）
定点数：decimal：定点数在mysql内部是用字符串形式存储的。所以要比浮点数更加精确，适合表示货币等精确数据。（摘自《深入浅出MySQL》）
mysql> create table t2(id1 float(5,2) default NULL,id2 double(5,2) NULL,id3 decimal(5,2) default NULL); Query OK, 0 rows affected (1.04 sec) mysql> desc t2; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id1 | float(5,2) | YES | | NULL | | | id2 | double(5,2) | YES | | NULL | | | id3 | decimal(5,2) | YES | | NULL | | +-------+--------------+------+-----+---------+-------+ 3 rows in set (0.97 sec) mysql>
我们使用的float和double的精度用法并不是标准的用法，所以精度要求很高的重要数据不建议使用
无论是定点类型还是浮点类型，如果用户指定的精度超出范围，则会进行四舍五入处理。
BIT（位）类型，这个是用来存放二进制的数据，BIT(m)这里的m范围是1~64，读取的时候我们要使用bin()或者hex()，用select是查不出数据的。
二、日期时间类型
|
日期和时间类型 |
字节 |
最小值 |
最大值 |
|
Date年月日 |
4 |
1000-01-01 |
9999-12-31 |
|
Datetime年月日时分秒 |
8 |
1000-01-01 00:00:00 |
9999-12-31 23:59:59 |
|
Timestamp时区对应时间 |
4 |
19700101080001 |
20380119111407 |
|
Time单独表示时间 |
3 |
-838:59:59 |
838:59:59 |
|
Year单独表示年 |
1 |
1901 |
2155 |
DATE、TIME、DATETIME是经常使用的3中日期类型
mysql> create table t3(d date,t time,dt datetime); Query OK, 0 rows affected (0.72 sec) mysql> desc t3; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.19 sec) mysql> insert into t3 values(now(),now(),now()); Query OK, 1 row affected, 1 warning (1.04 sec) mysql> select * from t3; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2017-03-03 | 06:51:28 | 2017-03-03 06:51:28 | +------------+----------+---------------------+ 1 row in set (0.00 sec) mysql>
还有就是TIMESTAMP，他的取值范围19700101080001到2038的某一天，范围相对比较小，而且这个和时区有关系。
三、字符串类型
|
字符串类型 |
字节 |
描述 |
|
char(m) |
M |
M为0-255之间的整数 |
|
varchar(m) |
|
M为0-65535之间的整数，值的长度为+1个字节 |
|
tinytext |
|
允许长度0-255字节，值为长度+2个字节 |
|
text |
|
允许长度0-65535字节，值为长度+2个字节 |
|
mediumtext |
|
允许长度0~167772150字节值为长度+3个字节 |
|
longtext |
|
允许长度0~4294967295字节值为长度+4个字节 |
这个我们最常用到，我们谈谈char和varchar的区别：主要区别就是存储的方式不同char的长度固定为创建表时的声明的长度，而varchar是可变长字符串，在检索的时候，char删除了尾部的空格而varchar则保留这些空格。（摘自《深入浅出MySQL》）
mysql> create table t4(v varchar(4),c char(4)); ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 24 Current database: lianxi Query OK, 0 rows affected (0.88 sec) mysql> insert into t4 values('ab ','ab ');------------------------->这里要插入空格 Query OK, 1 row affected (0.30 sec) mysql> select length(v),length(c) from t4; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 3 | 2 |--------------------------------->char类型少了个字节 +-----------+-----------+ 1 row in set (0.09 sec) mysql> select concat(v,'+'),concat(c,'+') from t4;------------>添加+，让效果更佳明显 +---------------+---------------+ | concat(v,'+') | concat(c,'+') | +---------------+---------------+ | ab + | ab+ | +---------------+---------------+ 1 row in set (0.06 sec) mysql>
如果某一项中设置的是varchar(50)那么对英文当然是50那么对中文呢utf-8的中文占3个字节那么,这个varchar(50)是不是只能存16个汉字了? 不是这样的，mysql低版本之前是这样的，但是5.0以后就不是了mysql varchar(50) 不管中文还是英文都是存50个的，但是一个表中所有varchar字段的总长度跟编码有关，如果是utf-8，那么大概65535/3，如果是gbk，那么大概65535/2编码长度限制字符类型若为gbk，每个字符最多占2个字节字符类型若为utf8，每个字符最多占3个字节。
ENUM和SET类型：
enum一般是多选一，我们可以做性别选择，set是多个成员可以选择多个。
mysql> create table t5(sex enum('man','woman')); Query OK, 0 rows affected (0.11 sec) mysql> insert into t5 values('man'); Query OK, 1 row affected (0.07 sec) mysql> insert into t5 values('men'); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t5; +------+ | sex | +------+ | man | | | +------+ 2 rows in set (0.00 sec) mysql> show warnings; Empty set (0.00 sec) mysql> insert into t5 values('men'); Query OK, 1 row affected, 1 warning (0.02 sec) mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1265 | Data truncated for column 'sex' at row 1 |-------------------->看警告 +---------+------+------------------------------------------+ 1 row in set (0.00 sec)
mysql> insert into t5 values('man,woman');
uery OK, 1 row affected, 1 warning (0.04 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'sex' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> create table t6(raw set('a','b','c'));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into t6 values('a,b'); Query OK, 1 row affected (0.00 sec) mysql> select * from t6； +------+ | raw | +------+ | a,b | +------+ 1 row in set (0.00 sec)