mysql数据类型详解

2018年5月14日 2,222次阅读 1人点赞

在复习mysql数据库的增删改查之前，我们先来看看mysql中的数据类型，只有深入了解了mysql的数据类型，我们才能更好的设计及使用mysql数据库。
mysql数据库的数据可以分为三个类型：数值型、时间日期型、字符型。

数值型

数值型又分为整数型和和小数型

整数型

数据类型|字节数|带符号最小值|带符号最大值|不带符号最小值|不带符号最大值
-|-|-|-|-|-
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|18446744073709551616
带符号的BIGINT其实是一个天文数字,9223372036854775807以byte为例可以表示8589934592GB = 8388608TB = 8192PB，以毫秒为例可以表示292471208年
所以在实际使用中我们要合理的定义数据类型，以免造成存储资源的浪费

测试：

create table test_int(
    -> num tinyint);
Query OK, 0 rows affected

mysql> insert into test_int values(-55);
Query OK, 1 row affected

mysql> insert into test_int values(255);
Query OK, 1 row affected

mysql> insert into test_int values(355);
Query OK, 1 row affected

mysql> select * from test_int;
+-----+
| num |
+-----+
| -55 |
| 127 |
| 127 |
+-----+

可以看到，默认的数据是有符号的，想无符号只需在定义字段时使用unsigned即可，例如”num tinyint unsigned”

如上，虽然插入了超过定义数据范围的值，mysql却没有报错，只是将数据变为最大值，这是因为没有开启严格模式的原因。

如何开启？
1.可以通过执行SQL语句来开启，但是只对当前连接有效

set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

2.通过修改MySQL的配置文件，在配置文件my.ini中查找sql-mode，将此行修改成为：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

重启mysql即可
开启严格模式后会对超出数据定义范围的数据报错，如下:

 insert insert into test_int values(255);
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into test_int values(255)' at line 1

浮点型

浮点型有一下两种

数据类型	字节数	解释
float	4	单精度浮点型
double	8	双精度浮点型

 mysql> create table test_float(
    -> num float(6,3));

insert into test_float values(200.444);
Query OK, 1 row affected

mysql> insert into test_float values(200.4444);
Query OK, 1 row affected
mysql> insert into test_float values(200.4445);
Query OK, 1 row affected
mysql> insert into test_float values(2000.4445);
1264 - Out of range value adjusted for column 'num' at row 1

select * from test_float;
+---------+
| num     |
+---------+
| 200.444 |
| 200.444 |
| 200.445 |
+---------+
3 rows in set

float(M,D)、double(M、D)的用法规则：
– D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即200.4444四舍五入为200.444，200.4445四舍五入为200.445
– M表示浮点型数据总共的位数，M=6则表示总共支持六位，即小数点前只支持三位数，所以我们并没有看到2000.4445这条数据的插入，因为插入报错了
当我们不指定M、D的时候，会按照实际的精度来处理。

定点型

float、double类型存在精度丢失问题，即写入数据库的数据未必是插入数据库的数据，而decimal无论写入数据中的数据是多少，都不会存在精度丢失问题，这就是我们要引入decimal类型的原因，decimal类型常见于银行系统、互联网金融系统等对小数点后的数字比较敏感的系统中。

例如：

create table test_decimal (
    float_num float(10, 2),
    double_num double(20, 2),
    decimal_num decimal(20, 2)
)

insert into test_decimal values(1234567.66, 1234567899000000.66, 1234567899000000.66);
insert into test_decimal values(1234567.66, 12345678990000000.66, 12345678990000000.66);

select * from test_decimal;
+------------+----------------------+----------------------+
| float_num  | double_num           | decimal_num          |
+------------+----------------------+----------------------+
| 1234567.63 |  1234567899000000.50 | 1234567899000000.66  |
| 1234567.63 | 12345678990000000.00 | 12345678990000000.66 |
+------------+----------------------+----------------------+

可以看到float和double存在丢失精度的问题，而decimal不会
decimal和float/double的区别：

float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的
decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)

日期类型

MySQL支持五种形式的日期类型：date、time、year、datetime、timestamp

数据类型	字节数	格式	备注
date	3	yyyy-MM-dd	存储日期值
time	3	HH:mm:ss	存储时分秒
year	1	yyyy	存储年
datetime	8	yyyy-MM-dd HH:mm:ss	存储日期+时间
timestamp	4	yyyy-MM-dd HH:mm:ss	存储日期+时间，可作时间戳

测试：

create table test_time (
    date_value date,
    time_value time,
    year_value year,
    datetime_value datetime,
    timestamp_value timestamp
) engine=innodb charset=utf8;

insert into test_time values(now(), now(), 2018, now(), now());

select * from test_time;
+------------+------------+------------+---------------------+---------------------+
| date_value | time_value | year_value | datetime_value      | timestamp_value     |
+------------+------------+------------+---------------------+---------------------+
| 2018-05-14 | 16:16:22   |       2018 | 2018-05-14 16:16:22 | 2018-05-14 16:16:22 |
+------------+------------+------------+---------------------+---------------------+

其中比较重要的是datetime与timestamp两种类型的区别：

datetime占8个字节，timestamp占4个字节
由于大小的区别，datetime与timestamp能存储的时间范围也不同，datetime的存储范围为1000-01-01 00:00:00——9999-12-31 23:59:59，timestamp存储的时间范围为19700101080001——20380119111407
datetime默认值为空，当插入的值为null时，该列的值就是null；timestamp默认值不为空，当插入的值为null的时候，mysql会取当前时间
datetime存储的时间与时区无关，timestamp存储的时间及显示的时间都依赖于当前时区

在实际工作中，一张表往往我们会有两个默认字段，一个记录创建时间而另一个记录最新一次的更新时间，这种时候可以使用timestamp类型来实现：

create_time timestamp default current_timestamp comment "创建时间",
update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",

字符型

字符型包含：char、varchar，text、blob

char和varchar类型

char和varchar的区别：
– char是固定长度字符串(1个字节)，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844
– char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3
– MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

mysql> create table test_string (
    char_value char(5),
    varchar_value varchar(5)
)

insert into test_string values('a', 'a');
insert into test_string values(' a', ' a');
insert into test_string values('a ', 'a ');
insert into test_string values(' a ', ' a ');

mysql> select * from test_string;
+------------+---------------+
| char_value | varchar_value |
+------------+---------------+
| a          | a             |
|  a         |  a            |
| a          | a             |
|  a         |  a            |
+------------+---------------+

mysql> select length(char_value),length(varchar_value) from test_string;
+--------------------+-----------------------+
| length(char_value) | length(varchar_value) |
+--------------------+-----------------------+
|                  1 |                     1 |
|                  2 |                     2 |
|                  1 |                     2 |
|                  2 |                     3 |
+--------------------+-----------------------+

可以看到char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会

varchar存贮研究

MySQL要求一个行的定义长度不能超过65535即64K
对于未指定varchar字段not null的表，会有1个字节专门表示该字段是否为null
varchar(M)，当M范围为0<=M<=255时会专门有一个字节记录varchar型字符串长度，当M>255时会专门有两个字节记录varchar型字符串的长度，把这一点和上一点结合，那么65535个字节实际可用的为65535-3=65532个字节
所有英文无论其编码方式，都占用1个字节，但对于gbk编码，一个汉字占两个字节，因此最大M=65532/2=32766；对于utf8编码，一个汉字占3个字节，因此最大M=65532/3=21844，上面的结论都成立
举一反三，对于utfmb4编码方式，1个字符最大可能占4个字节，那么varchar(M)，M最大为65532/4=16383，可以自己验证一下
同样的，上面是表中只有varchar型数据的情况，如果表中同时存在int、double、char这些数据，需要把这些数据所占据的空间减去，才能计算varchar(M)型数据M最大等于多少。

varchar、text和blob

类型	存储大小
tinyblob ,tinytext	256字节
blob ,text	64k
mediumblob，mediumtext	16M
longblob，longtext	4G

以上最大存储大小的实际值要稍小，一般为少一个字节

text和blob两种数据类型，它们的设计初衷是为了存储大数据使用的，因为MySql单行最大数据量为64K。

先说一下text，text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：

M>255时转为tinytext
M>500时转为text
M>20000时转为mediumtext

所以过大的内容varchar和text没有区别，同时varchar(M)和text的区别在于：

单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来
text可以指定text(M)，但是M无论等于多少都没有影响
text不允许有默认值，varchar允许有默认值

varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使mediumtext（16M）或者longtext（4G）。

至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。

作品采用知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议进行许可

影梧的博客

mysql数据类型详解

数值型

整数型

浮点型

定点型

日期类型

字符型

char和varchar类型

varchar存贮研究

varchar、text和blob

发表回复取消回复

数值型

整数型

浮点型

定点型

日期类型

字符型

char和varchar类型

varchar存贮研究

varchar、text和blob

发表回复 取消回复

发表回复取消回复