MySQL(二) — 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

注：平时所用BIGINT均为有符号的。

1、创建带有无符号字段的表
    CREATE TABLE test_unsigned (a INT UNSIGNED,b INT UNSIGNED);
    insert into test_unsigned values(1,2);
    select a-b from test_unsigned; # 由于使用无符号的类型，a-b=-1，超出了int类型精度，所以此SQL报错

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

    mysql> select * from test_int_n;
    +--------+
    | a      |
    +--------+
    |   0001 |
    | 123456 |
    +--------+
#######################
int(N)中的 N 是显示宽度， 不表示 存储的数字的 长度 的上限。
zerofill 表示当存储的数字 长度 < N 时，用 数字0 填充左边，直至补满长度 N
当存储数字的长度 超过N时 ，按照 实际存储 的数字显示

3、自增

下列语法有错误么？
 create table test_auto_increment(a int auto_increment); #错误，自增必须加在主键上
 create table test_auto_increment(a int auto_increment primary key);# 正确

以下SQL运行结果是什么？
insert into test_auto_increment values(NULL); # 1
insert into test_auto_increment values(0);
insert into test_auto_increment values(-1); #-1
insert into test_auto_increment values(null),(100),(null),(10),(null);

#####
如果插入值为0或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

1、排序规则，bin将字符串中的每一个字符用二进制数据存储，区分大小写，cici不区分大小写，ci为case insensitive的缩写，即大小写不敏感;
    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';   --结果是什么？

    set names utf8mb4 collate utf8mb4_bin
     
    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

注：datetime无时区，timestamp有时区的

create table test_time(a timestamp,b datetime);
insert into test_time values(now(),now());
select * from test_time;
set time_zone='+00:00';
select * from test_time;

四、json类型

1、JSON类型的使用 
    create table json_user (uid int auto_increment,data json,primary key(uid));
    insert into json_user values (null, '{"name":"li","age":18,"address":"beijing"}' );
    insert into json_user values (null,'{"name":"zhang","age":28,"mail":"11@163.com"}');

2、JSON类型相关函数
    json_extract：抽取
        select json_extract(data, '$.name'),json_extract(data, '$.address') from json_user;
        +------------------------------+---------------------------------+
        | json_extract(data, '$.name') | json_extract(data, '$.address') |
        +------------------------------+---------------------------------+
        | "li"                         | "beijing"                       |
        | "zhang"                      | NULL                            |
        +------------------------------+---------------------------------+

    JSON_OBJECT： 将对象转为json
        insert into json_user values ( null,json_object("name", "wangwu", "email", "12@qq.com", "age",35) );

    json_insert： 插入数据
    json_merge：  合并数据并返回
    其他函数：https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

3、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) 
     );