MySQL数据类型

一、Int类型

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

1.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;--运行行出错

SELECT a-b FROM test_unsigned;–运行行出错如下图

1.2 int(N)是什么？
演示：

CREATE TABLE test_int_n(a INT(4) ZEROFILL);
INSERT INTO test_int_n VALUES(1);
INSERT INTO test_int_n VALUES(123456);

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

1.3 自动增长的面试题
1.3.1 create table test_auto_increment(a int auto_increment);语法有错误吗？
答：有错误，只能有一个自动列，它必须被定义为键。应该定义为 create table test_auto_increment(a int auto_increment primary key);

1.3.2 当把a列设置为自增之后执行 INSERT INTO test_auto_increment VALUES(NULL);语句会有何结果？

1.3.3 执行insert into test_auto_increment values(0);语句会有何结果？

1.3.4 执行insert into test_auto_increment values(-1);语句会有何结果？

1.3.5 执行insert into test_auto_increment values(null),(100),(null),(10),(null)语句会有何结果？

二、字符类型

类型	说明	N的含义	是否有字符集	最大长度
CHAR(N)	定长字符	字符	是	255
VARCHAR(N)	变长字符	字符	是	16384
BINARY(N)	定长二进制字节	字节	否	255
VARBINARY(N)	变长二进制字节	字节	否	16384
TINYBLOB(N)	二进制大对象	字节	否	256
BLOB(N)	二进制大对象	字节	否	16K
MEDIUMBLOB(N)	二进制大对象	字节	否	16M
LONGBLOB(N)	二进制大对象	字节	否	4G
TINYTEXT(N)	大对象	字节	是	256
TEXT(N)	大对象	字节	是	16K
MEDIUMTEXT(N)	大对象	字节	是	16M
LONGTEXT(N)	大对象	字节	是	4G

2.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');
SELECT * FROM test_ci WHERE a = 'a'; 

三、时间类型

日期类型	占用空间	表示范围
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	-838:59:59 ~ 838:59:59

3.1 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';

四、JSON类型

4.1 新建一个表

create table json_user (
 uid int auto_increment,
 data json,
 primary key(uid)
 );

4.2 插入数据

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"
 }');

假如插入的不是JSON格式的会怎么样
4.3 json_extract 抽取

SELECT json_extract('[10, 20, [30, 40]]', '$[1]');

  SELECT json_extract(DATA, '$.name'),json_extract(DATA, '$.address') FROM json_user;

4.4 JSON_OBJECT 将对象转为json

SELECT json_object("name", "enjoy", "email", "enjoy.com", "age",35);

```sql
INSERT INTO json_user VALUES (NULL, json_object(“name”, “enjoy”, “email”, “enjoy.com”, “age”,35) );

![在这里插入图片描述](https://img-blog.csdnimg.cn/20190704100317756.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FpbWFzaGk2MjA=,size_16,color_FFFFFF,t_70)
**6.5 json_insert 插入数据**
```sql
   UPDATE json_user SET DATA = json_insert(DATA, "$.address_2", "xiangxue") WHERE uid = 1;

4.5 json_merge合并数据并返回

其他函数参考：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

4.6 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) 
 );

注意gen_col列中的king、peter是带双引号的
SELECT json_extract(DATA,"$.name") AS username FROM test_inex_1 WHERE gen_col=“king”;执行此sql查不出内容

给查询条件"king"再加上双引号就能查询到数据

EXPLAIN SELECT json_extract(DATA,"$.name") AS username FROM test_inex_1 WHERE gen_col="king"

如上给查询条件"king"再加上双引号才能查询到数据显然是不太合理的，那么怎么样创建索引才能避免这种问题呢，答案是使用json_unqote()，如下

CREATE TABLE test_index_2 (
 DATA json,
 gen_col VARCHAR(10) generated always AS (
 json_unquote( 
 json_extract(DATA, "$.name")
 )),
 KEY idx(gen_col)
 );

以上内容来自“享学课堂”笔记课后巩固练习。