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 从-231到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 就会报错：不允许存这样的值。

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

  1. 若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。
  2. 若四舍五入后，整数部分超出范围，则 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 '时间'
)

  1. 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。

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

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

  1. 也可以 使用不带有冒号的字符串或者数字，格式为 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个字节
  1. 在使用 char 类型可以预先定义字符串长度。如果不指定字符串长度，默认是1个字符。
  2. 在存储时，如果数据的实际长度比 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个字节
  1. varchar(m) 定义时，必须指定长度 m，否则报错。
  2. 检索 varchar 类型的字段数据时，会保留数据尾部的空格。
  3. 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 说明：

  1. MySql 4.0 以后，varchar(20)：指的是20字节，指定存放 utf8 的汉字6个。
  2. MySql 5.0 以后，varchar(20)：指的是20个字符，就是使用了61个字节。
  3. 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 类型
  1. 在 MySQL 中，text 用来保存文本类型的字符串，总共包含4种类型，分别为：tinytext，text，mediumtext 和 longtext 类型。
  2. 在向 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;

注意：

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

建议：

  1. text 文本类型，可以存比较大的文本段，查询速度稍慢，因此不是特别大的内容，建议使用 char，varchar 来代替。
  2. 还有 text 类型不用加默认值，加了也没用。
  3. 而且 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 '枚举类型'
)
  1. 当 enum 类型包含 1 ~ 255 个成员时，需要1个字节的存储空间。
  2. 当 enum 类型包含 256 ~ 65535 个成员时，需要2个字节的存储空间。
  3. 注意：在给枚举列添加值的时候，只能是枚举中的一个值添加，只能是一个值，一个值（忽略大小写）。

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 '列表类型'
)
  1. 插入重复的 set 类型成员时，MySQL 会自动删除重复的成员。
  2. 向 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 类型的数据：
  1. 修改 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
  1. 往 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
  1. 删除 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 @   小林当  阅读(93)  评论(0)  编辑  收藏  举报
相关博文：
·  MySQL的数据目录
·  MYSQL索引优化与查询优化
·  【MySQL】03_数据类型
·  mysql数据类型
·  MySQL------数据类型重点关注
阅读排行：
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· 阿里巴巴 QwQ-32B真的超越了 DeepSeek R-1吗？
· 【译】Visual Studio 中新的强大生产力特性
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 【设计模式】告别冗长if-else语句：使用策略模式优化代码结构
点击右上角即可分享
微信分享提示
AI IDE Trae