三、Mysql数据类型

一、Int类型

/前后分别为有符号和无符号大小

类型	字节	最小值	最大值
TINYINT	1	-128/0	127/255
SMALLINT	2	-32768/0	32767/65535
MEDIUMINT	3	-8388608/0	8388607/16777215
INT	4	-2147483648/0	2147483647/4294967295
BIGINT	8	-9223372036854775808/0	9223372036854775807/18446744073709551615

1、有符号和无符号数据

create table test_unsigned(a int unsigned, b int unsigned);
insert into test_unsigned values(1, 2);
select b - a from test_unsigned;
select a - b from test_unsigned; --运行出错

在日常使用中一般使用bigInt有符号数据

2、INT(N)是什么?

• int(N)中的 N 是显示宽度， 不表示 存储的数字的 长度 的上限。
• zerofill 表示当存储的数字 长度 < N 时，用 数字 0 填充左边，直至补满长度 N
• 当存储数字的长度 超过 N 时 ，按照实际存储 的数字显示

create table test_int_n(a int(4) zerofill); 
insert into test_int_n values(1);
insert into test_int_n values(123456);
select * from test_int_n;

结果为 ：0001、123456

3、小甜点（面试可能会问到）

// 会报错，自增只能是主键
create table test_auto_increment(a int auto_increment);
// 正常
create table test_auto_increment(a int auto_increment primary key);

// 插入主键为1的数据
insert into test_auto_increment values(NULL);
// 插入不成功
insert into test_auto_increment values(0);
// 可以插入成功
insert into test_auto_increment values(-1);
// 插入的结果为1，10，100，101，102
insert into test_auto_increment values(null),(100),(null),(10),(null)

二、字符类型

字符的排序规则

// 结果为1
select 'a' = 'A';

create table test_ci (a varchar(10), key(a)); 
insert into test_ci values('a');
insert into test_ci values('A');
// 结果为a,A
select * from test_ci where a = 'a';
// 修改排序规则后变为a
set names utf8mb4 collate utf8mb4_bin
 

三、时间类型

datatime 与 timestamp 区别

create table test_time(a timestamp, b datetime); 
insert into test_time values (now(), now()); 
select * from test_time;
// 查询时区
select @@time_zone;
// 修改时区
set time_zone='+00:00';
// 发现timestamp值改变了，它是和时区有关联的，并且大小只到2038年左右
select * from test_time;

四、Json类型

// 新建表
create table json_user ( 
uid int auto_increment,
data json,
primary key(uid)
);

//插入两条数据
insert into json_user values ( null, 
'"name":"lison", "age":18, "address":"enjoy"}' );
insert into json_user values ( null,
'{"name":"james", "age":28, "mail":"james@163.com"}');

1、json_extract提取函数

提取json串中的值

select
json_extract(data, '$.name'), json_extract(data, '$.address')
from json_user;

2、JSON_OBJECT将对象转为 json

insert into json_user values (
 null,
json_object("name", "enjoy", "email", "enjoy.com", "age",35) )

3、json_insert插入数据

update json_user set data = json_insert(data, "$.address_2", "xiangxue") where uid = 1;

4、json_merge合并数据并返回

select
json_merge(
json_extract(data, '$.address'), json_extract(data, '$.address_2')) 
from json_user where uid = 1;

其他函数：mysql官方地址

5、Json索引

JSON 类型数据本身 无法直接 创建索引，需要将需要索引的JSON 数据 重新生成虚拟列(Virtual Columns) 之后，对该列进行索引

create table test_inex_1(
data json,
gen_col varchar(10) generated always as (json_extract(data, '$.name')), 
index idx (gen_col)
);


insert into test_inex_1(data) values ('{"name":"king", "age":18, "address":"cs"}'); insert into test_inex_1(data) values ('{"name":"peter", "age":28, "address":"zz"}');


select * from test_inex_1;

select json_extract(data,"$.name") as username from test_inex_1 where gen_col='"king"';