mysql系列-数据库字段类型

数字类型

• 工作中常用的int为bigint，通常使用有符号

  有符号的意思是有正负数，通常大家觉得基本都不用负数，但是在如字段a-字段b的情况下出现负数，查询就会报错

• int(num) 问题，这个num代表显示位数，当数值不足这个num的位数时，则前面补0，否则全显示

  只有在命令行中才能看到效果

  

  

  create table test (a int auto_increment primary key);
  

  auto_increment关键字必须加在主键上

insert into test values (null), (100), (null), (10), (null);

最终从查询结果为1，10，100，101，102
如果自增主键，null，0都是递增，其他有效值正确

​

字符类型

除了CHAR和VARCHAR，其他都是字节

select ‘a’ = “A” 为1，如用户名Lisa 和 LIAS

• CHAR，VARCHAR

Create table test (a varchar(10), key(a));
Insert into test values("a");
Insert into test values("A");
select * from test where a = "a";
查询结果为两条。

决定这个的是在创建数据库时的排序规则所定的

后缀ci ：

后缀bin：二进制存储，区分大小写

• TEXT

时间类型

• DATETIME 和 TIMESTAMP的区别

  主要是时区的区别

  DATE，没有时区，有日期，没时间

  DATETIME没有时区，有日期和时间

  TIMESTAMP有时区，有日期和时间

JSON

sql操作

@json：局部变量

@@conf：全局变量（环境变量信息等）

show VARIABLES like "%datadir%";
select @@datadir;
两个执行结果相同

-- 创建表，和json字段
create table json (
	id bigint auto_increment, 
	data json, 
	primary key(id)
);

-- 插入数据
insert into json values (
	null,
	'{
		"name": "张三",
		"age": "123",
		"addr": "北京"
	}'
);

-- 查询数据
select * from json;

-- json基础查询
select json_extract ("[1,2,3,[1,2]]", "$[1]");
-- 返回结果2
select json_extract ("[1,2,3,[4,5]]", "$[3][1]");
-- 返回结果5

-- json数据查询，返回指定字段
select 
	json_extract(data, "$.addr"),
	json_extract(data, "$.name")
	from json;

-- json_object函数
select json_object ("addr", "北京");
-- 返回：将（）中的内容转成json，两两对应。例：（key1，key1value，key2，key2value,...）

-- 使用json_object函数插入数据
insert into json values (
	null,
	json_object("name", "王五", "addr", "上海", "age", "455")
);

-- json_insert
set @json = '{"a", 100, "b": [1,2]}';
select json_insert(@json, "$.a", 1, "$.c", "[true, false]");
-- 结果为：'{"a", 100, "b": [1,2], "c": "[true, false]"}'
-- 格式化数据，并对变量数据和新增数据做处理，变量中已有的值不会被替换，没有的会追加，如$.a,1不会提替换变量中a:100

-- json_merge
select json_merge('{"a": 1}', '{"b", 2}');
-- 合并数据，结果为{"a": 1, "b", 2}
-- 如果字段key一样，将合并为数组。例：
	-- select json_merge('{"a": 1}', '{"a", 2}');
	-- 结果为：{"a": [1, 2]}

json 索引

json是不能直接创建索引

通过虚拟列创建索引

创建虚拟列

-- 虚拟列使用
-- 使用函数json_extract创建的虚拟列查询参数时，需要带""。如：select json_extract(data, "$.name") from test_index where gen_col = '"张三"';
-- 使用函数
create table test_index(
	data json,
	gen_col varchar(10) generated always as (json_extract(data, "$.name")),
	index idx(gen_col)
);

-- 插入数据
insert into test_index(data) values('{"name": "张三", "age": "15"}');
insert into test_index(data) values('{"name": "李四", "age": "15"}');

-- 通过索引查询数据
select json_extract(data, "$.name") from test_index where gen_col = "张三";