MySQL数据类型

数据类型

1. MySQL 中的数据类型

类型	类型举例
整数类型	TINYINT , SMALLINT , MEDIUMINT , INT BIGINT
浮点类型	FLOAT , DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR , TIME , DATE , DATETIME , TIMESTAMP
文本字符串类型	CHAR , VARCHAR , TINYTEXT , TEXT , MEDIUMTEXT , LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY , VARBINARY , TINYBLOB , BLOB , MEDIUMBLOB , LONGBLOB
JSON类型	JSON
空间数据类型

常见数据类型的属性如下：

关键字	含义
NULL	数据列可包括NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号针对整数，只能表示正数，不能为负数
CHARSET	指定一个字符集

常用的数据类型如下：

数据类型	描述
INT	从-2^31到231-1的整数数据。存储大小为4个字节
CHAR(size)	定长字符数据。若未指定，默认为1个字符，最大长度255
VARCHAR(size)	可变长字符数据，根据字符串实际长度保存，必须指定长度
DECLMAL(M,D)	高精度，占用M+2个字节，M = 整数位+小数位，D = 小数位。D<=M<=65 ，0<=D<=30，最大取值范围与 DOUBLE 相同
DATE	日期型数据，格式 "YYYY-MM-DD"
BLOB	二进制形式的长文本数据，最大可达 4G
TEXT	长文本数据，最大可达 4G

2. 整数类型

整数类型一共有5种：tinyint，smallint，mediumint，int，bigint

各个 "整数类型" 的取值范围如下：

整数类型	字节	有符号数取值范围	无符号数取值范围
tinyint	1	-128 ~ 127	0 ~ 255
smallint	2	-32768 ~ 32767	0 ~ 65535
mediumint	3	-8388608 ~ 8388607	0 ~ 16777215
int，integer	4	-2147483648 ~ 2147483647	0 ~ 4294967295
bigint	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

可选属性：

UNSIGNED：无符号类型（非负数）所有的 "整数类型" 都有一个可选的书写 UNSIGNED（无符号属性），无符号整数类型的最小取值为 0。所以在需要保存非负数整数值时，可以将整数类型设置为无符号类型。
CREATE TABLE IF NOT EXISTS supconit_data_type_int (
	id tinyint UNSIGNED PRIMARY KEY auto_increment COMMENT '主键id',
	id_ext tinyint COMMENT 'id扩展'
)
ZEROFILL：0 填充。（如果某列是 zerofill ，那么 MySQL 会自动为当前列添加 unsigned 属性），如果指定了 zerofill，表示不够 M 位时，用 0 在左边填充，如果超过 M 位，只要不超过数据存储范围即可。
ALTER TABLE supconit_data_type_int MODIFY COLUMN id_ext INT(10) ZEROFILL;

各个整数类型的适用场景：

tinyint：一般用于枚举数据，年龄等...，设定取值范围很小且固定的场景。
smallint：用于较小范围的统计数据，例如：工厂的固定资产等...。
mediumint：用于较大整数的计算，比如车站，机场的客流量等公共场所...。
int，integer：取值范围足够大，一般情况下不用考虑超限问题，订单号等...。
bigint：当处理特别巨大的整数时才会用到。双十一，大型网站点击量，巨额资金交易等...

注意：在选择 "整数类型" 时，需要考虑长时间累计因素，可到达的一个数值范围。再进行选择 "整数类型"，以保证系统长久时间的运行。

整数类型-显式宽度：

整数类型中 int(10) 或者列的长度：这表示的是该 "整数类型" 指定的显示宽度，指定能够显示的数值中数字的个数（有符号时，负号占一位，正数不计符号位）。
显示宽度和 "整数类型" 的取值范围是无关的。
如果插入了大于显示宽度的值，只要该值不超过该类型的取值范围，数值依然可以插入，而且能显示出来。
再次强调显示宽度只是用于显示，并不能限制数据的取值范围和占用空间。例如：INT(3) 依然会占用4个字节的存储空间，允许存储的最大值还是 INT 整型所允许的最大值。

ALTER TABLE supconit_data_type_int ADD COLUMN year_int INT(4) UNSIGNED COMMENT '年';
例如，向 year_int 字段插入一个数值 19999，当使用 select 查询的时候，MySQL 显示的将是完整带有5位数字的 19999，而不是4位数字的值。

给大家算一算：

4个字节和8个字节在不同数据量下存储空间差别：

1000 行 * 4 = 4 MB ，10万行 * 4 = 400 MB，100万行 * 4 = 4GB。

1000 行 * 8 = 8 MB ，10万行 * 8 = 800 MB，100万行 * 8 = 8GB。

可以看到到达百万级别之后，不同的数据类型在存储空间差别对于计算机来说是巨大的！

这仅仅只是一个列而已。

3. 浮点类型

"浮点数" 和 "定点数" 类型的特点是可以处理小数，MySQL 支持的浮点数类型：float， double。

float：单精度浮点数
double：双精度浮点数

浮点类型	字节	有符号数取值范围	无符号数取值范围
float	4	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0 ~ (1.175 494 351 E-38，3.402 823 466 E+38)
double	8	(-1.797 693 134 862 315 7 E+308，-2.225073858507 2014E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0 ~ (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)

float 和 double 的区别：float 占用字节数少，取值范围小；double 占用字节数多，取值范围也大。

浮点数类型的使用：

float：folat(m,d)

double：double(m,d)

说明：m："精度"，d："标度"。m = 整数位+小数位，整数位 = m - d，小数位 = d。"精度" 的取值范围：d<=m<=255，"标度" 的取值范围：0<=d<=30。

float 和 double 类型在不指定(m,d) 时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。

如果存储时，整数部分超出了范围，MySQL 就会报错：不允许存这样的值。

如果存储时，小数点部分若超出范围，会出现以下情况：

若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。

若四舍五入后，整数部分超出范围，则 MySQL 报错，并拒绝处理。

float 类型：

CREATE TABLE
IF NOT EXISTS supconit_data_type_float (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_float FLOAT (5, 2) COMMENT '浮点数类型'
)

说明：data_float列，长度为=5，整数位长度=3，小数位长度=2。

double 类型：

ALTER TABLE supconit_data_type_double ADD COLUMN data_doudle DOUBLE (5, 2) UNSIGNED COMMENT '浮点数类型';

说明：data_double列，长度为=5，整数位长度=3，小数位长度=2。

注意：在使用 "浮点数" 时，要特别注意误差问题，因为 "浮点数" 是不准确的，所以我们要避免使用 "=" 来判断两个数是否相等。

4. 定点数类型

MySQL 中的 "定点数类型" 只有 DECIMAL 一种类型。

数据类型	字节数	含义
decimal(m,d)	m+2字节	有效范围由 m 和 d 决定

说明：m："精度"，d："标度"。m = 整数位+小数位，整数位 = m - d，小数位 = d。"精度" 的取值范围： d<=m<=65，"标度" 的取值范围： 0<=d<=30。

当不指定 decimal 的精度和标度时，其默认为：deciaml(10,0)。当数据的超出精度范围时，则 MySQL 同样会进行四舍五入处理（和浮点数逻辑相同）。

定点数类型在 MySQL 内部是已 "字符串" 的形式存储的。

定点数类型的使用：

CREATE TABLE
IF NOT EXISTS supconit_data_type_decimal (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_decimal DECIMAL (5, 2) COMMENT '定点数类型'
)

说明：data_decimal列，长度为=5，整数位长度=3，小数位长度=2，占用7个字节。

浮点数与定点数：

"浮点数" 相对于 "定点数"，更为适合用于取值范围大，允许存在误差的计算场景。

"定点数" 取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（例如涉及金额计算的场景）。

5. 位类型

"bit" 类型中存值的是二进制值，类似：010110。

二进制字符串类型	长度	长度范围	占用空间
bit(m)	m	1<=m<=64	约为 "(m+7)/8" 个字节

bit类型，如果没有指定 (m)，默认是1位。这个1位，表示只能存1位的为二进制值。这里 "(m)" 是表示二进制的位数（位数最小值为1，最大值为64）。

CREATE TABLE
IF NOT EXISTS supconit_data_type_bit (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_bit BIT (1) COMMENT '二进制数据类型'
)

6. 日期和时间类型

MySQL 有多种表示 "日期" 和 "时间" 的数据类型，版本之间可能存在差异，MySQL 8.0 版本支持的 "日期" 和 "时间"类型主要有：year，time，date，datetime 和 timestamp 类型。

类型	名称	字节	日期格式	最小值	最大值
year	年	1	YYYY或YY	1901	2155
time	时间	3	HH:MM:SS	-838:59:59	838:59:59
date	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
datetime	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
timestamp	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

可以看到，不同数据类型表示的时间内容不同，取值范围不同，而且占用的字节数也不一样，要根据实际需要选择。

疑问：为什么时间类型 time 的取值范围不是 -23:59:59 ~ 23:59:59 呢？

答：原因是在 MySQL 设计的 time 类型，不光表示一天之内的时间，而是可以用来表示一个时间间隔，所以这个时间间隔可以超过24个小时。

year 类型

year类型用来表示 "年份"，格式为：'YYYY' ，最小值 1901，最大值 2155，仅需 1字节的存储空间。

CREATE TABLE
IF NOT EXISTS supconit_data_type_year (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_year YEAR COMMENT '年份'
)

输入：0000 会默认转为：2000 年。

date 类型

date类型表示日期，没有时间部分，格式为 'YYYY-MM-DD'|'YYYYMMDD' （YYYY：年份，MM：月份，DD：日期），最小值 1000-01-01，最大值 9999-12-03，需要3个字节的存储空间。在插入date类型数据时，必须满足一定的格式条件。

CREATE TABLE
IF NOT EXISTS supconit_data_type_date (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_date DATE COMMENT '日期'
)

time 类型

time 类型表示时间，格式为 'HH:MM:SS'|HHMMSS（HH：小时，MM：分钟，SS：秒），最小值 -838:59:59，最大值 838:59:59，需要3个字节的存储空间。在插入time类型数据时，必须满足一定的格式条件。

CREATE TABLE
IF NOT EXISTS supconit_data_type_time (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_time time COMMENT '时间'
)

MySQL 中可以使用带有冒号的字符串，比如： 'D HH:MM:SS'，'HH:MM:sS'，'HH:MM'，'D HH:MM'，'D HH' 或 'SS' 格式，都能被正确的插入time 类型的字段中。其中 D 表示天，其最小值为0，最大值为34。如果使用带有 D 格式的字符串插入time类型的字段时，D 会被转化为小时，计算格式为 D*24+HH。

当使用带有冒号并且不带 D 的字符串表示时间时，表示当天的时间。从左到右依次解析为：时:分:秒。如果插入一个不合法的字符串或者数字，MySQL 在存储数据时，会将其自动转化为 00:00:00 进行存储。

比如：12:10 表示 12:10:00，而不是 00:12:10。

也可以使用不带有冒号的字符串或者数字，格式为 HHMMSS。

比如：1210，MySQL 会将最右边的两位解析成秒，表示 00:12:10，而不是 12:10:00。

datetime 类型

datetime 类型表示日期时间，在格式上是 date 类型和 time 类型的组合，格式为 'YYYY-MM-DD HH:MM:SS'|YYYYMMDDHHMMSS，最小值 1000-01-01 00:00:00，最大值 9999-12-31 23:59:59，需要 8 个字节的存储空间。在插入datetime类型数据时，必须满足一定的格式条件。

CREATE TABLE
IF NOT EXISTS supconit_data_type_datetime (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_date_time datetime COMMENT '日期时间'
)

timestamp 类型

一句话：占用空间小，且带时区的 datetime 类型。

说明：该类型存储数据的时候需要对时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用 timestamp 存储的同一个时间值，在不同的时区查询时会显示不同的时间（底层使用的毫秒数存储的时间）。

timestamp 和 datetime 的区别：

timestamp 存储空间比较小，表示的日期时间范围也比较小。

底层存储方式不同，timestamp 底层存储的是毫秒值（例如：1970 -01-01 00:00:00 0 毫秒的毫秒值）。

两个日期比较大小或日期计算时，timestamp 更方便，更快。

timestamp 和时区有关。timestamp 会根据用户的时区不同，显示不同的结果。而 datetime 则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的

7. 文本字符串类型

MySQL 中，文本字符串总体上分为：char，varchae，tinytext，text，mediumtext，longtext，enum，set 等类型

文本字符串类型	值的长度	长度范围	占用的存储空间
char(m)	m	0<=m<=255	m个字节
varchar(m)	m	0<=m<=65535	m+1个字节
tinytext	l	0<=l<=255	l+2个字节
text	l	0<=l<=65535	l+2个字节
mediumtext	l	0<=l<=16777215	l+3个字节
longtext	l	0<=l<=4294967295	l+4个字节
enum	l	1<=l<=65535	1或2个字节
set	l	0<=l<=64	1，2，3，4或8个字节

char 类型

字符串（文本）类型	特点	长度（字符长度）	长度范围	占用的存储空间
char(m)	固定长度	m	0<=m<=255	m个字节

在使用 char 类型可以预先定义字符串长度。如果不指定字符串长度，默认是1个字符。
在存储时，如果数据的实际长度比 char 类型声明的长度小，则会在右侧填充（尾部）空格以达到指定长度。MySQL 检索数据时会去除尾部的空格。

CREATE TABLE
IF NOT EXISTS supconit_data_type_char (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_char CHAR(5) COMMENT '固定字符串'
)

varchar 类型

字符串（文本）类型	特点	长度（字符长度）	长度范围	占用的存储空间
varchar(m)	可变长度	m	0<=m<=65535	(实际长度)+1个字节

varchar(m) 定义时，必须指定长度 m，否则报错。
检索 varchar 类型的字段数据时，会保留数据尾部的空格。
65535 = 1个字节长度表示 + 1个字节 null + 其余的字节。

CREATE TABLE
IF NOT EXISTS supconit_data_type_varchar (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_varchar VARCHAR (5) COMMENT '可变字符串'
)

各个版本 varchar 说明：

MySql 4.0 以后，varchar(20)：指的是20字节，指定存放 utf8 的汉字6个。

MySql 5.0 以后，varchar(20)：指的是20个字符，就是使用了61个字节。

MySql 8.0 之后，varchar(20)：指的是20个字符，一个 utf8 的汉字占用3或4个字节（字节：20 * 3 + 1/20 * 4 + 1）。

char 和 varchar 那个更好

类型	特点	空间上	时间上	适用场景
char(m)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
varchar(m)	可变长度	节省存储空间	效率低	非 char 的情况

要根据具体 "存储引擎" 选择：MyISAM 用 char，InnoDB 用 varchar。

MyISAM 存储引擎：MyISAM数据表，最好使用固定长度（char）的数据列代替可变长度（varchar）的数据列，是表静态化，数据检索更快，空间换时间。

InnoDB 存储引擎，建议使用 varchar 类型。因为对于 InnoDB 数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于 char 平均占用的空间多于 varchar，所以除了简短并且固定长度的字符串列，其他都考虑使用 varchar。这个节省空间，对磁盘I/O和数据存储总量比较好。

text 类型

在 MySQL 中，text 用来保存文本类型的字符串，总共包含4种类型，分别为：tinytext，text，mediumtext 和 longtext 类型。
在向 text 类型的列保存和查询数据时，系统自动按照实际长度存储和返回，不需要预先定义长度。

文本字符串类型	特点	长度	长度范围	占用的存储空间
tinytext	小文本，可变长度	l	0<=l<=255	l+2个字节
text	文本，可变长度	l	0<=l<=65535	l+2个字节
mediumtext	中等文本，可变长度	l	0<=l<=16777215	l+3个字节
longtext	大文本，可变长度	l	0<=l<=4294967295(4G)	l+4个字节

CREATE TABLE
IF NOT EXISTS supconit_data_type_text (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_tinytext TINYTEXT COMMENT '小文本',
	data_text text COMMENT '文本',
	data_mediumtext MEDIUMTEXT COMMENT '中等文本',
	data_longtextt LONGTEXT COMMENT '大文本'
)
# 查询验证
SELECT
	LENGTH(data_tinytext),
	LENGTH(data_text),
	LENGTH(data_mediumtext),
	LENGTH(data_longtextt)
FROM
	supconit_data_type_text;
SELECT * FROM supconit_data_type_text;

注意：

text 类型在检索时是不会去除尾部空格的。
由于长度不确定，MySQL 不允许 text 类型的字段做主键。遇到这种情况，只能采用 char 或 varchar。

建议：

text 文本类型，可以存比较大的文本段，查询速度稍慢，因此不是特别大的内容，建议使用 char，varchar 来代替。
还有 text 类型不用加默认值，加了也没用。
而且 text 和 blob 类型的数据删除后容易导致 "空洞"（存储空间碎片）。所以频繁使用的表不建议包含 text 和 blob 类型，建议单独分出去，单独用一张表（垂直分表）。

8. 枚举类型

enum 枚举类型，enum 类型的取值范围需要在定义列时进行指定，可以包含 0 个或多个成员。存储列值时，枚举类型只允许从成员中选取单个值，不能一次选取多个值。

文本字符串类型	长度	长度范围	占用的存储空间
enum	l	1<=l<=65535	1或2个字节

CREATE TABLE
IF NOT EXISTS supconit_data_type_enum (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_enum enum ('后裔', '虞姬', '蒙犽','公孙离','百里守约') COMMENT '枚举类型'
)

当 enum 类型包含 1 ~ 255 个成员时，需要1个字节的存储空间。

当 enum 类型包含 256 ~ 65535 个成员时，需要2个字节的存储空间。

注意：在给枚举列添加值的时候，只能是枚举中的一个值添加，只能是一个值，一个值（忽略大小写）。

9. set 类型

set 类型是字符串，set 类型的取值范围需要在定义字段时进行指定，可以包含 0 个或多个成员，但成员个数的上限为 64 个。设置字段值时，可以取取值范围内的 0 个或多个值。

文本字符串类型	成员个数范围（l 表示实际成员个数）	占用的存储空间
SET	1<=l<=8	1个字节
SET	9<=l<=16	2个字节
SET	17<=l<=24	3个字节
SET	25<=l<=32	4个字节
SET	33<=l<=64	8个字节

CREATE TABLE
IF NOT EXISTS supconit_data_type_set (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_set SET ('赵云', '凯', '李白','云缨','宫本武藏') COMMENT '列表类型'
)

插入重复的 set 类型成员时，MySQL 会自动删除重复的成员。

向 set 类型的字段插入 set 成员中不存在的值时，MySQL 会抛出错误。

10. 二进制字符串类型

MySQL 中的二进制字符串类型包括：binary，varbinary，tinyblob，blob，mediumblob 和 longblob类型。主要是用于存储，图片，音频和视频文件等二进制数据。

binary 类型

二进制字符串类型	特点	值的长度	占用空间
binary(m)	固定长度	m (0<=m<=255)	m 个字节

binary 类型 "固定长度" 的二进制字符串，可以预先定义长度，如果未指定，默认存储 1 个字节（它和char很像）。

CREATE TABLE
IF NOT EXISTS supconit_data_type_binary (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_binary BINARY (5) COMMENT '二进制字符串'
)
# 插入语句
INSERT INTO supconit_data_type_binary(data_binary) VALUES('林'); # 乱码
INSERT INTO supconit_data_type_binary(data_binary) VALUES('aabd');

varbinary 类型

二进制字符串类型	特点	值的长度	占用空间
varbinary(m)	可变长度	m (0<=m<=65535)	m+1个字节

varbinary 类型为 "可变长度" 的二进制字符串，必须指定长度 m，否则报错。

CREATE TABLE
IF NOT EXISTS supconit_data_type_varbinary (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_varbinary VARBINARY (5) COMMENT '二进制字符串'
)
# 插入语句
INSERT INTO supconit_data_type_varbinary(data_varbinary) VALUES('林'); # 乱码
INSERT INTO supconit_data_type_varbinary(data_varbinary) VALUES('aabd');

blob 类型

在实际工作中，往往不会在 MySQL 数据库中使用 blob 类型存储数据。通常会将图片，音频和视频文件存储到服务器的磁盘上，将图片，音频和视频以及文件的访问路径存储到数据库中。

二进制字符串类型	值的长度	长度范围	占用空间
tinyblob	l	0<=l<=255	l+1个字节
blob	l	0<=l<=35535 (64KB)	l+2个字节
mediumblob	l	0<=L<=16777215 (16MB)	l+3个字节
longblob	l	0<=l<=4294967295 (4GB)	l+4个字节

CREATE TABLE
IF NOT EXISTS supconit_data_type_blob (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_tinyblob TINYBLOB COMMENT '二进制字符串-小',
	data_blob BLOB COMMENT '二进制字符串-中',
	data_mediumblob MEDIUMBLOB COMMENT '二进制字符串-大',
	data_longblob LONGBLOB COMMENT '二进制字符串-超大'
)

说明：使用 blob 和 text 类型会引起一些问题，在执行大量的删除或更新操作的时候。会留下很大的 "空洞"（存储空间碎片），影响性能。

11. JSON 类型

MySQL 5.7 中，就已经支持 json 数据类型。在 MySQL 8 版本中，json 类型提供了可以进行自动验证的 json文档和优化的存储结构，使得在 MySQL 中存储和读取 json 类型的数据更加方便和高效。

CREATE TABLE
IF NOT EXISTS supconit_data_type_json (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_json json COMMENT 'json数据'
)
# 插入数据
INSERT INTO supconit_data_type_json(data_json) VALUES ('{"name":"xld","age":"18","address":{"sheng":"河南"}}'),('{"name":"中控","age":"60","address":{"sheng":"杭州"}}')

查询表中 json 类型的数据：

SELECT
	id,
	data_json ->> '$.name' AS c_name,
	data_json ->> '$.address.sheng' AS address_sheng
FROM
	supconit_data_type_json;

补充 - json 数组存储与查询：

CREATE TABLE
IF NOT EXISTS supconit_data_type_json_array (
	id INT UNSIGNED PRIMARY KEY auto_increment COMMENT '主键',
	data_json_array json COMMENT 'json数组'
)
# 插入数据
INSERT INTO supconit_data_type_json_array (data_json_array)
VALUES('[{"name":"中控","age":"60","address":{"sheng":"杭州"}},{"name":"xld","age":"18","address":{"sheng":"河南"}}]'),
('{"name":"中控","age":"60","address":{"sheng":"杭州"}}')

查询表中 json 类型的数据（json 数组）：

SELECT
	id,
	data_json_array,
	data_json_array ->> '$[0]' o,
	data_json_array ->> '$[1]' o1,
	data_json_array ->> '$[1].age' o1_age,
	data_json_array ->> '$[1].address.sheng' o1_address_sheng,
	data_json_array ->> '$.age' AS age,
	data_json_array ->> '$.address.sheng' AS address_sheng
FROM
	supconit_data_type_json_array

扩展 - json 多层数组：

INSERT INTO supconit_data_type_json_array (data_json_array)
VALUES
	(
'[
{"name":"中控","age":"60","address":{"sheng":"杭州"},"like":["钱","大钱"]},
{"name":"xld","age":"18","address":{"sheng":"河南"},"like":["java","mysql"]}
]'
	)

查询 SQL：

SELECT
	id,
	data_json_array ->> '$[0].like[0]' AS o_like,
	data_json_array ->> '$[1].like[1]' AS o1_like
FROM
	supconit_data_type_json_array

修改表中 json 类型的数据：

修改 JSON 中的某个字段：

UPDATE supconit_data_type_json_array SET data_json_array = json_set(data_json_array,'$.age','70')  WHERE id = 2;
#
UPDATE supconit_data_type_json_array SET data_json_array = json_replace(data_json_array,'$.age','70')  WHERE id = 2;

# 再次查询
SELECT
 id,
 data_json_array,
 data_json_array ->> '$[0]' o,
 data_json_array ->> '$.age' AS age,
 data_json_array ->> '$.address.sheng' AS address_sheng
FROM
 supconit_data_type_json_array

往 JSON 中插入一个新的字段：

UPDATE supconit_data_type_json_array SET data_json_array = json_set(data_json_array,'$.code','211225199701013339')  WHERE id = 2;
#
UPDATE supconit_data_type_json_array SET data_json_array = json_insert(data_json_array,'$.code','211225199701013339')  WHERE id = 2;

# 再次查询
SELECT
 id,
 data_json_array,
 data_json_array ->> '$[0]' o,
 data_json_array ->> '$.age' AS age,
 data_json_array ->> '$.address.sheng' AS address_sheng,
 data_json_array ->> '$.code' AS `code`
FROM
 supconit_data_type_json_array

删除 JSON 中的属性：

UPDATE supconit_data_type_json_array SET data_json_array = json_remove(data_json_array,'$.code')  WHERE id = 2;

# 再次查询
SELECT
 id,
 data_json_array,
 data_json_array ->> '$[0]' o,
 data_json_array ->> '$.age' AS age,
 data_json_array ->> '$.address.sheng' AS address_sheng,
 data_json_array ->> '$.code' AS `code`
FROM
 supconit_data_type_json_array

json_set() 支持替换现有值，并且支持添加不存在的值。
json_insert() 插入值而不替换现有值。
json_replace() 仅替换现有值。
json_remove() 删除指定的属性。

update 数据全部修改：

UPDATE supconit_data_type_json_array SET data_json_array = '[{"name":"中控","age":"60","address":{"sheng":"杭州"}},{"name":"xld","age":"18","address":{"sheng":"河南"}}]'  WHERE id = 2;

更多关于 JSON 类型的操作请参照官网：https://dev.mysql.com/doc/refman/8.0/en/json-modification-functions.html

12. 空间类型

呃....

13. 几点建议

列如果为非负数，必须是无符号（unsigned）的。
小数类型必须使用 decimal ，禁止使用 float 和 double（如果超过了 decimal 的范围，就将数据拆成整数和小数并分开存储）。
如果存储的字符串长度几乎相等，使用 char 类型。
varchar 是可变字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，建议使用 text，并独立出来一张表，用主键对应，从而避免影响表的索引效率。

最后：我想说，为列（字段）选择合适的数据类型，除了说明你很厉害之外，也是对数据库性能一种提升和优化。

一个老师教的网站：https://www.cnblogs.com/catfeel/p/16477797.html

posted @ 2023-11-22 13:41 小林当阅读(57) 评论(0) 编辑收藏举报

刷新页面返回顶部

小林当