mysql的数据类型和字符集

MySQL的数据类型

MySQL数据库支持的数据类型主要有以下几种：

整型
浮点型
字符
BLOB型
枚举和集合类型
JSON类型（MySQL5.7新增加的支持）

整型

整数类型是数据库中最基本的数据类型。标准SQL中支持INTEGER和SMALLINT这两类整数类型。MySQL数据库除了支持这两种类型之外，还扩展支持了TINYINT, MEDIUMINT和BIGINT。

MySQL中各种整型占据的字节数和取值范文如下：

整数类型	字节数	无符号数取值范围	有符号数取值范围(添加一位符号为，把无符号数除以2)	默认显示宽度
TINYINT	1	0~2^8	-128~127	4
SMALLINT	2	0~2^16	-32768~32767	6
MEDIUMINT	3	0~2^24	-8388608~8388607	9
INT	4	0~2^32	-2147483648~2147483647	11
INTEGER	4	0~2^32	-2147483648~2147483647	11
BIGINT	8	0~2^64	-（2^64）/2~[(2^64)/2] -1	20

INT类型和INTEGER类型的字节数和取值范围是一样，在MySQL中INT类型和INTEGER类型是一样的。

MySQL支持数据类型的名称后面指定该类型的显示宽度。

数据类型 （显示宽度）#其中，数据类型参数是整数数据类型的名称，显示宽度参数是指定宽度的数值。#在建表时，若是没有指定显示宽度，则MySQL使用默认的显示宽度：mysql> create table tb3(a tinyint, b smallint, c mediumint, d int, e bigint);Query OK, 0 rows affected (0.02 sec)mysql> show create table tb3;    # 查看默认的显示宽度+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                                                                                                        |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| tb3   | CREATE TABLE `tb3` (  `a` tinyint(4) DEFAULT NULL,  `b` smallint(6) DEFAULT NULL,  `c` mediumint(9) DEFAULT NULL,  `d` int(11) DEFAULT NULL,  `e` bigint(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)mysql>

整型的zerofill参数和auto_increment参数：

zerofill，在插入数据长度没有达到默认显示宽度时，用0填充。

mysql> create table tb4(a () zerofill, b ( rows affected (> insert into tb4 values(, row affected (>  *+------+------+| a    | b    |+------+------+|  |     |+------+------+ row  set (mysql> insert into tb4 values(10000,20000);          #插入超过指定宽度的数据Query OK, 1 row affected (0.00 sec)mysql> select * from tb4;                            #仍然会正常显示+-------+-------+| a     | b     |+-------+-------+|  0001 |     2 || 10000 | 20000 |+-------+-------+2 rows in set (0.00 sec)mysql> insert into tb4 values(123456789123,123456789123);         #插入超过默认宽度的数据会报错ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql>

auto_increment参数，自增长，需要注意的是自增长序列必须为索引，一张表中，只能有一个自增长的字段。

mysql> create table tb5(a () auto_increment, b  (> create table tb5(a () auto_increment primary key, b  rows affected ( sec)mysql> insert into tb5(b) values(5);         #插入字段b数值Query OK, 1 row affected (0.00 sec)mysql> select * from tb5;                    #自增长列是从1开始的+---+------+| a | b    |+---+------+| 1 |    5 |+---+------+1 row in set (0.00 sec)#自增长列的下一个值，是从这个列中最大的一个数值+1，开始的，如下：mysql> insert into tb5(b) values(8);         #插入两条数据Query OK, 1 row affected (0.01 sec)mysql> insert into tb5(b) values(9);         #插入数据之后，字段a的值为3Query OK, 1 row affected (0.00 sec)mysql> insert into tb5 values(-7, 21);       #插入字段a的数值为-7Query OK, 1 row affected (0.01 sec)mysql> insert into tb5(b) values(23);        #再插入一条数据，字段a的值为4，而不是-6Query OK, 1 row affected (0.00 sec)mysql> select * from tb5;+----+------+| a  | b    |+----+------+| -7 |   21 ||  1 |    5 ||  2 |    8 ||  3 |    9 ||  4 |   23 |+----+------+5 rows in set (0.00 sec)#可以设置自增长字段的初始值mysql> create table tb5( a int(4) auto_increment primary key, b int) auto_increment=5;   #设置初始值为5Query OK, 0 rows affected (0.01 sec)mysql> insert into tb5(b) values(23);Query OK, 1 row affected (0.01 sec)mysql> select * from tb5;+---+------+| a | b    |+---+------+| 5 |   23 |+---+------+1 row in set (0.00 sec)mysql> insert into tb5(b) values(21);Query OK, 1 row affected (0.01 sec)mysql> select * from tb5;+---+------+| a | b    |+---+------+| 5 |   23 || 6 |   21 |+---+------+2 rows in set (0.00 sec)

浮点型

MySQL中使用浮点类型和定点数类型来表示小数。浮点数类型包括单精度浮点数和双精度浮点数。定点数类型就是DECIMAL型。

类型	字节数	负数取值范围	非负数取值范围
float	4	暂无	暂无
double	8	暂无	暂无
DECIMAL(M,D)或者DEC(M,D)	M+2	暂无	暂无

MySQL可以指定浮点数和定点数的精度。

数据类型 (M,D)#M称为精度，是数据的总长度，小数点不占位置；D参数称为标度，指小数点后的长度

浮点数在保存的时候经常会出一些意想不到的结果，建议使用DEC保存。

https://www.cnblogs.com/phpfensi/p/8143313.html创建mysql数据表的时候，经常会遇到存储小数(浮点数)的情况，如：价格，重量，身高等。目前大的公司流行三种存储方案：1、将数据扩大10的倍数达到使用整数类型存储目的。比如价格，我们经常以分为单位进行存储，也就是将数据扩大100倍，这样元改成分存储。重量可以用克为单位，如果克还是小数，就以毫克，微克等单位进行存储。2、使用decimal类型的方式存储比如价格，我们可以使用两位小数的精度进行存储。强烈不建议采用float或者double的类型存储，会存在精度损失的问题，在以后做值比较的时候，容易出现不正确的结果。3、小数和整数部分分开存储。比如价格3.14，我们存成两个字段，一个字段存储3，一个字段存储14，一般情况下用的少。当存储的数据范围超出decimal的范围时，可以将数据按照整数和小数拆分。

MySQL中小数的存储

MySQL小数的存储，可以参照上面提到的方法！

日期与时间类型

日期与时间类型是为了方便在数据库中存储日期和时间而设定的。MySQL中有多种表示日期和时间的数据类型。其中，year表示时间，date表示日期，time表示实际；datetime和timestamp表示日期和时间。

类型	字节数	取值范围	零值
YEAR	1	1901~2155	0000
DATE	4	1000-01-01~9999~12-31	0000:00:00
TIME	3	-839:59:59~838:59:59	00：00:00
DATETIME	8	1000-01-01 00:00:00 ~9999~12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP（包含时区信息）	4	1970-01-01 08:00:01~2038-01-19 11:14:07	0000-00-00 00:00:00

每一种时间类型都有一个有效的范围。介绍一下Time类型。

TIME类型使用3个字节来表示时间。MySQL中以HH:MM:SS的形式显示TIME类型的值。其中，HH表示时，MM表示分，取值范围为0~59；SS表示秒，取值范围为0~59。虽然小时的范围是0~23，但是为表示某种特殊需要的时间间隔，将TIME类型的范围扩大了，而且还支持负数。

TIME类型的字段赋值表示方法如下：

“D HH:MM:SS”格式的字符串表示。其中，D表示天数，取值范围为0~34。保存时，小时的值等于(D*34+HH). mysql> create table tb6(d time);Query OK, 0 rows affected (0.02 sec)mysql> insert into tb6 values("2 11:30:50");Query OK, 1 row affected (0.01 sec)mysql> select * from tb6;+----------+| d        |+----------+| 59:30:50 |+----------+1 row in set (0.00 sec)mysql>

在向数据库输入年份，日期的时候，MySQL有一些便捷的方法，但是推荐使用标准的输入，即没有缺省的输入！

字符类型

char类型和varchar类型都是在创建表时指定了最大长度，其基本形式如下：

字符串类型(M)        # M表示该字符串的最大长度为M，M指的是字符的个数，而不是字节！char(M):表示该字段的字符串的最大长度为M，M取值范围为0~255之间的任意值。存储的占用的字节也是M字节固定不变的！varhchar（M）:表示字段的字符串最大长度为M，M取值范围为0~65535之间的任意值。指定为varchar类型之后，其长度可以在0到M（最大长度）之间。其占用的字节为，字符的实际字节数再加1.一个实例：mysql> create table tb7(a char(5), b varchar(5));Query OK, 0 rows affected (0.04 sec)mysql> insert into tb7 values("a", "a"), ("bb","bb");  #插入数值Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select a, b from tb7;    +------+------+| a    | b    |           #这个记录中a占用5个字节，b占用2个字节+------+------+| a    | a    || bb   | bb   |           #这个记录中a占用5个字节，b占用3个字节。+------+------+2 rows in set (0.00 sec)#插入汉字mysql> insert into tb7 values("aaaaa","bbbbb");Query OK, 1 row affected (0.01 sec)mysql> insert into tb7 values("上下左右中","上下左右中");Query OK, 1 row affected (0.00 sec)mysql> select a, b from tb7;+-----------------+-----------------+| a               | b               |+-----------------+-----------------+| aaaaa           | bbbbb           |      #这个记录a占用了5个字节，b占用了5+1个字节| 上下左右中        | 上下左右中       |      #这个记录中a占用了5*3=15个字节，b占用了5*3+1=16个字节+-----------------+-----------------+2 rows in set (0.00 sec)mysql>

特别注意：通过以上的实例可以发现，char(M)和varchar(M)中的M指的是字符的个数，不是字节！

　　　　　　那么M的取值范围，那个范围究竟是字节还是字符呢？

TEXT类型

TEXT类型是一种特殊的字符串类型。TEXT只保存字符数据。包含以下四种类型：

类型	允许长度	存储空间
TINYTEXT	0~255字节	存储的实际字节+2个字节
TEXT	0~65535字节	存储的实际字节+2个字节
MEDIUMTEXT	0~2^24-1字节	存储的实际字节+3个字节
LONGTEXT	0~2^32-1字节	存储的实际字节+4个字节

在实际存储时，可以根据不同需求选择不同的类型存储。

ENUM类型

ENUM类型又称为枚举类型。在创建表时，ENUM类型的取值范围就以列表的形式指定了。

属性名  ENUM(,

set类型

set类型与enum类型一样，区别在于set类型可以选取多个值，而enum类型只能选择一个值。

插入记录时，set字段中的元素顺序是无关紧要的，存入MySQL数据库后，数据库系统会自动按照定义时的顺序显示。

mysql   tb9(str1 ("a", "b", "c","d"), num ("one", "two","three")   rows affected (   tb9(str1,num)  row affected (    str1  num        a,d   one,three  row   (   tb9(num)  row affected (    str1  num        a,d   one,three    one,two    rows   (   tb9(str1)  (HY000): Field  doesn

二进制类型

二进制类型是在数据库中存储二进制数据的数据类型。包含以下几种：

项目	说明
BINARY(M)	字节长度为M，允许长度为0~M的定长二进制字符串
VARBINARY(M)	允许长度为0~M的变长二进制字符串，字节数为数值的字节数+1
BIT(M)	M位二进制数据，M最大值为64
TINYBLOB	可变长二进制数据，最多为255个字节
BLOB	可变长二进制数据，最多为(2^16-1)个字节
MEDIUMBLOB	可变长二进制数据，最多为（2^24-1）个字节
LONGBLOB	可变长二进制数据，最多为（2^32-1）个字节

BLOB类型是一种特殊的二进制类型。BLOB类型可以用来数量很大的二进制数据，如图片等。但是通常情况下，可以把图片和文档存储在文件系统中，在BLOB中存入对应图片，文档的路径地址。但是这样，访问的速度可能会慢一点吧！

JSON类型：

在MySQL5.7中新增加了对JSON的支持。与在字符串列中存储json相比，数据类型有以下几点优势：

自动验证在JSON列中存储的JSON文档，无效的列会报错。
优化的存储格式。存储在JSON列中的JSON文档将转换为内部格式，以允许快速读取文档元素。当服务器稍后必须读取以此二进制格式存储的JSON值时，不需要从文本表示中解析该值。二进制格式的结构使服务器能够直接通过键或数组索引查找子对象或嵌套值，而无需在文档中读取它们之前或之后的所有值。存储JSON所需的空间与LONGBLOB和LONGTEXT类型大致相同。请务必记住，JSON列中存储的任何JSON文档的大小都限制为max_allowed_packet系统变量的值。（当服务器在内存中内部操作JSON值时，它可能大于此值;当服务器存储时，该限制适用。）
JSON列没有默认值

虚构一张表，来说明MySQL5.7中JSON的用法：

  auto_increment

还可以插入JSON数组和JSON对象的嵌套：

INSERT INTO json_test(userinfo) VALUES('[5,{"id":1, "name":"lfy"}, ["a","b"]]');

如果插入无效的JSON数组和JSON对象将会报错。

除MySQL数据类型之外，还有一组SQL函数可用于对JSON值进行操作。

mysql> select * from json_test;+----+--------------------------------------------------------------------+| id | userinfo                                                           |+----+--------------------------------------------------------------------+|  1 | {"name": "libai", "email": "libai@163.com", "address": "china"}    ||  2 | {"name": "Obama", "email": "libai@gmail.com", "address": "miguo"}  ||  3 | {"name": "putin", "email": "putin@gmail.com", "address": "russia"} ||  4 | ["wangxz", "guangzhou", "wangxz@163.com"]                          |+----+--------------------------------------------------------------------+4 rows in set (0.00 sec)

JSON_TYPE函数返回JSON的类型：

mysql> select json_type(userinfo) from json_test where id = 1;  #返回的是JSON对象类型+---------------------+| json_type(userinfo) |+---------------------+| OBJECT              |+---------------------+1 row in set (0.00 sec)mysql> select json_type(userinfo) from json_test where id = 4;   #返回的是数组类型+---------------------+| json_type(userinfo) |+---------------------+| ARRAY               |+---------------------+1 row in set (0.00 sec)mysql>

在向表中插入JSON类型时，可以借助于JSON_ARRAY()函数和JSON_OBJECT()函数，得到JSON数据类型。

mysql  json_array("a",  json_array("a", , now())                 row   (   json_test(userinfo)  json_array("a",  row affected (  Duplicates:   Warnings:   json_object("name","tump","address","miguo","email","tump json_object("name","tump","address","miguo","email","tump.com")  {"name": "tump", "email": "tump.com", "address": "miguo"}        row   (   json_test(userinfo)  json_object("name","tump","address","miguo","email","tump row affected (  Duplicates:   Warnings:

JSON_MERGE获取两个或多个文档的组合结果

mysql> select json_merge('["a","b"]', '{"key":"value"}');+--------------------------------------------+| json_merge('["a","b"]', '{"key":"value"}') |+--------------------------------------------+| ["a", "b", {"key": "value"}]               |+--------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql>

可以将JSON值分配给用户定义的变量，但是定义的变量不能是JSON类型。

mysql> set @j = json_object("key","value");Query OK, 0 rows affected (0.00 sec)mysql> select @j;+------------------+| @j               |+------------------+| {"key": "value"} |+------------------+1 row in set (0.00 sec)mysql> select charset(@j), collation(@j);+-------------+---------------+| charset(@j) | collation(@j) |+-------------+---------------+| utf8mb4     | utf8mb4_bin   |+-------------+---------------+1 row in set (0.00 sec)#通过转换生成的字符串具有以上的字符集和排序规则，utf8mb4_bin是二进制排序规则，所以区分大小写。mysql> select json_array("x") = json_array("X");+-----------------------------------+| json_array("x") = json_array("X") |+-----------------------------------+|                                 0 |+-----------------------------------+1 row in set (0.00 sec)mysql> select json_valid("null"),json_valid("NULL"),json_valid("true"),json_valid(True);+--------------------+--------------------+--------------------+------------------+| json_valid("null") | json_valid("NULL") | json_valid("true") | json_valid(True) |+--------------------+--------------------+--------------------+------------------+|                  1 |                  0 |                  1 |                0 |+--------------------+--------------------+--------------------+------------------+1 row in set (0.00 sec)所以这些值null， true，false还是以小写的形式。

把引号插入到JSON对象中

#使用json_object对象时，需要使用反斜线转义引号才能插入mysql   json_test(userinfo) (json_object("mascot", "The mysql mascot  row affected (   json_test(userinfo)  json_object("mascot", "The mysql mascot  row affected (  Duplicates:   Warnings:    json_test(userinfo) ( (): Invalid JSON : "Missing a comma   after an object member." at position   value      json_test(userinfo) ( row affected (

JSON值的规范化，合并和自动包装：

当解析一个字符串时，发现它是一个有效的JSON文档时，它就会被标准化。

json_object的过程就是一个标准化的过程。

json_merge将组合的后面的一个数组连接到前面一个数值的末尾，组合为单个数据。

mysql> select json_merge('[1,2]', '["a","b"]');+----------------------------------+| json_merge('[1,2]', '["a","b"]') |+----------------------------------+| [1, 2, "a", "b"]                 |+----------------------------------+1 row in set, 1 warning (0.00 sec)mysql>

合并时多个对象生成单个对象。如果多个对象具有相同的键，则生成的合并对象中该键的值是包含该键所有值的数值。

mysql> select json_merge('{"a":1,"b":2}', '{"a":3, "c":4}');+-----------------------------------------------+| json_merge('{"a":1,"b":2}', '{"a":3, "c":4}') |+-----------------------------------------------+| {"a": [1, 3], "b": 2, "c": 4}                 |+-----------------------------------------------+1 row in set, 1 warning (0.00 sec)mysql>

自动将两个字符合并为数组：

mysql> select json_merge("1", "2");+----------------------+| json_merge("1", "2") |+----------------------+| [1, 2]               |+----------------------+1 row in set, 1 warning (0.00 sec)mysql>

通过将对象自动包装为数值，来合并数组和对象：

mysql> select json_merge('[1,2]', '{"a":1,"b":2}');+--------------------------------------+| json_merge('[1,2]', '{"a":1,"b":2}') |+--------------------------------------+| [1, 2, {"a": 1, "b": 2}]             |+--------------------------------------+1 row in set, 1 warning (0.00 sec)mysql>

对JSON的操作：

JSON_EXTRACT---对于提取JSON文档的一部分或修改JSON文档的函数非常有用。

mysql  json_extract( json_extract(, "$.name")  "wangxz"                                              row   (

上面提到可以使用$符合来表示当前选中的JSON文档。在选择时$符合可以和通配符结合。

mysql  json_extract(, "$ json_extract(, "$")  ]                          row   (  json_extract(, "$ json_extract(, "$")                                                                    row   (  json_extract(, "$ json_extract(, "$")  {"id": , "name": "lfy"}                                           row   (  json_extract(, "$ json_extract(, "$.id")                                                                       row   (  json_extract(, "$ json_extract(, "$.name")  "lfy"                                                                   row   (  json_extract(, "$ json_extract(, "$")  {"id": , "name": "lfy"}                                              row   (  json_extract(, "$ json_extract(, "$")                                                           row   ( sec)

JSON_SET替换JSON文档中某个位置得数值，若是这个位置不存在，则在这个位置添加数值。

mysql> select userinfo from json_test WHERE id = 5;+-------------------------------------------+| userinfo                                  |+-------------------------------------------+| [5, {"id": 1, "name": "lfy"}, ["a", "b"]] |+-------------------------------------------+1 row in set (0.00 sec)#表中有如上数据，我们把name的值改为wxz，最后一个数值中加入“c”mysql> select json_set(userinfo, "$[1].name", "wxz", "$[2][2]", "c") from json_test WHERE id = 5;+--------------------------------------------------------+| json_set(userinfo, "$[1].name", "wxz", "$[2][2]", "c") |+--------------------------------------------------------+| [5, {"id": 1, "name": "wxz"}, ["a", "b", "c"]]         |+--------------------------------------------------------+1 row in set (0.00 sec)

JSON_INSERT添加新的数值，但是不替换旧数值！

mysql> select userinfo from json_test WHERE id in (5,11);+-------------------------------------------+| userinfo                                  |+-------------------------------------------+| [5, {"id": 1, "name": "lfy"}, ["a", "b"]] |+-------------------------------------------+1 row in set (0.01 sec)#注意insert的作用，只是添加了字符“c”，并没有更改name键的值mysql> select json_insert(userinfo, "$[1].name", "wxz", "$[2][2]", "c") from json_test WHERE id = 5;+-----------------------------------------------------------+| json_insert(userinfo, "$[1].name", "wxz", "$[2][2]", "c") |+-----------------------------------------------------------+| [5, {"id": 1, "name": "lfy"}, ["a", "b", "c"]]            |+-----------------------------------------------------------+1 row in set (0.00 sec)

JSON_REPLACE:替换旧值，但是不能添加新值。

mysql> select json_replace(userinfo, "$[1].name", "wxz", "$[2][2]", "c") from json_test WHERE id = 5;+------------------------------------------------------------+| json_replace(userinfo, "$[1].name", "wxz", "$[2][2]", "c") |+------------------------------------------------------------+| [5, {"id": 1, "name": "wxz"}, ["a", "b"]]                  |+------------------------------------------------------------+1 row in set (0.00 sec)mysql>

JSON_REMOVE删除选择的数值。

mysql> select json_remove(userinfo, "$[1].name", "$[2][1]") from json_test WHERE id = 5;+-----------------------------------------------+| json_remove(userinfo, "$[1].name", "$[2][1]") |+-----------------------------------------------+| [5, {"id": 1}, ["a"]]                         |+-----------------------------------------------+1 row in set (0.00 sec)mysql>