MySQL之常用数据类型

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_year_demo1(
    column1 YEAR      COMMENT '基本YEAR类型（默认YEAR(4)）',
    column2 YEAR(4)   COMMENT 'YEAR类型');
-- 查询创建表结构
    DESC type_demo.table_year_demo1;
    +---------+---------+------+-----+---------+-------+
    | Field   | Type    | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | column1 | year(4) | YES  |     | NULL    |       |
    | column2 | year(4) | YES  |     | NULL    |       |
    +---------+---------+------+-----+---------+-------+
-- 添加数据并查询
    INSERT INTO type_demo.table_year_demo1 VALUES('22','71'),(0,'0');
    SELECT * FROM type_demo.table_year_demo1;
    +---------+---------+
    | column1 | column2 |
    +---------+---------+
    |    2022 |    1971 |
    |    0000 |    2000 |
    +---------+---------+

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_date_demo1(
    column1 DATE      COMMENT '基本DATE类型');
-- 查询创建表结构
    DESC type_demo.table_date_demo1;
    +---------+------+------+-----+---------+-------+
    | Field   | Type | Null | Key | Default | Extra |
    +---------+------+------+-----+---------+-------+
    | column1 | date | YES  |     | NULL    |       |
    +---------+------+------+-----+---------+-------+
-- 添加数据并查询
    INSERT INTO type_demo.table_date_demo1 VALUES
        ('2022-09-13'),('2022/11/14'),('20221115'),(20221116);
    INSERT INTO type_demo.table_date_demo1 VALUES
        ('00-01-01'),('22/11/14'),('700101'),(991231);
    SELECT * FROM type_demo.table_date_demo1;
    +------------+
    | column1    |
    +------------+
    | 2022-09-13 |
    | 2022-11-14 |
    | 2022-11-15 |
    | 2022-11-16 |
    | 2000-01-01 |
    | 2022-11-14 |
    | 1970-01-01 |
    | 1999-12-31 |
    +------------+
-- 注：超出范围一律为0000-00-00
    INSERT INTO type_demo.table_date_demo1 VALUES('9999-12-32'),('99-12-32');
    +------------+
    | column1    |
    +------------+
    | 0000-00-00 |
    | 0000-00-00 |
    +------------+

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_time_demo1(
    column1 TIME      COMMENT '基本time类型');
-- 查询创建表结构
    DESC type_demo.table_time_demo1;
    +---------+------+------+-----+---------+-------+
    | Field   | Type | Null | Key | Default | Extra |
    +---------+------+------+-----+---------+-------+
    | column1 | time | YES  |     | NULL    |       |
    +---------+------+------+-----+---------+-------+
-- 添加数据并查询
    INSERT INTO type_demo.table_time_demo1 VALUES('2 22:32:14'),('15:36:22'),('12:18'),('2 12:42'),('3 06'),('42');
    SELECT * FROM type_demo.table_time_demo1;
    +----------+
    | column1  |
    +----------+
    | 70:32:14 |    (这70:32:14 由 2 * 24 +22 :32 14 得来)
    | 15:36:22 |
    | 12:18:00 |
    | 60:42:00 |
    | 78:00:00 |
    | 00:00:42 |
    +----------+
    INSERT INTO type_demo.table_time_demo1 VALUES(223214),('153622'),(1218),(NOW());
    +----------+
    | column1  |
    +----------+
    | 22:32:14 |
    | 15:36:22 |
    | 00:12:18 |
    | 16:59:23 |
    +----------+

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_datetime_demo1(
    column1 DATETIME      COMMENT '基本datetime类型');
-- 查询创建表结构
    DESC type_demo.table_datetime_demo1;
    +---------+----------+------+-----+---------+-------+
    | Field   | Type     | Null | Key | Default | Extra |
    +---------+----------+------+-----+---------+-------+
    | column1 | datetime | YES  |     | NULL    |       |
    +---------+----------+------+-----+---------+-------+
-- 插入数据
    INSERT INTO type_demo.table_datetime_demo1 VALUES 
        ('2021-01-01 06:50:30'), ('20210101065030'); 
    INSERT INTO type_demo.table_datetime_demo1 VALUES 
        ('99-01-01 00:00:00'),('990101000000'),('20-01-01 00:00:00'),('200101000000'); 
    INSERT INTO type_demo.table_datetime_demo1 VALUES 
        (20200101000000), (200101000000), (19990101000000), (990101000000); 
    INSERT INTO type_demo.table_datetime_demo1 VALUES (CURRENT_TIMESTAMP()), (NOW());
    SELECT * FROM type_demo.table_datetime_demo1;
    +---------------------+
    | column1             |
    +---------------------+
    | 2021-01-01 06:50:30 |
    | 2021-01-01 06:50:30 |
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 1999-01-01 00:00:00 |
    | 2022-11-13 17:36:52 |
    | 2022-11-13 17:36:52 |
    +---------------------+

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_timestamp_demo1(
    column1 TIMESTAMP      COMMENT '基本TIMESTAMP类型');
-- 查询创建表结构
    DESC type_demo.table_timestamp_demo1;
    +---------+-----------+------+-----+-------------------+-----------------------------+
    | Field   | Type      | Null | Key | Default           | Extra                       |
    +---------+-----------+------+-----+-------------------+-----------------------------+
    | column1 | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +---------+-----------+------+-----+-------------------+-----------------------------+
-- 插入数据
    INSERT INTO type_demo.table_timestamp_demo1 VALUES 
        ('1999-01-01 03:04:50'), ('19990101030405'),('99-01-01 03:04:05'),('990101030405'),
        ('2020@01@01@00@00@00'),('20@01@01@00@00@00'),(CURRENT_TIMESTAMP()),(NOW());
    SELECT * FROM type_demo.table_timestamp_demo1;
    +---------------------+
    | column1             |
    +---------------------+
    | 1999-01-01 03:04:50 |
    | 1999-01-01 03:04:05 |
    | 1999-01-01 03:04:05 |
    | 1999-01-01 03:04:05 |
    | 2020-01-01 00:00:00 |
    | 2020-01-01 00:00:00 |
    | 2022-11-13 21:49:44 |
    | 2022-11-13 21:49:44 |
    +---------------------+

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_td_demo1(
    column1 DATETIME      COMMENT '基本timestamp类型',
    column2 TIMESTAMP      COMMENT '基本timestamp类型');
-- 查询创建表结构
    DESC type_demo.table_td_demo1;
    +---------+-----------+------+-----+-------------------+-----------------------------+
    | Field   | Type      | Null | Key | Default           | Extra                       |
    +---------+-----------+------+-----+-------------------+-----------------------------+
    | column1 | datetime  | YES  |     | NULL              |                             |
    | column2 | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
    +---------+-----------+------+-----+-------------------+-----------------------------+
-- 插入2组数
    INSERT INTO type_demo.table_td_demo1 VALUES
    ('2022-11-11 14:22:22','2022-11-11 14:22:22'),(NOW(),NOW());
-- 基于同一时区的基本查询
    SELECT * FROM type_demo.table_td_demo1;
    +---------------------+---------------------+
    | column1             | column2             |
    +---------------------+---------------------+
    | 2022-11-11 14:22:22 | 2022-11-11 14:22:22 |
    | 2022-11-13 21:54:06 | 2022-11-13 21:54:06 |
    +---------------------+---------------------+
-- 更改时区信息（具体百度MySQL时区设置）
    SHOW VARIABLES LIKE '%time_zone';    -- 查询数据库时区
    SET time_zone = '+9:00';            -- 设置当前时区为东九区
-- 基于修改过时区的基本查询（基于东九区）
    SELECT * FROM type_demo.table_td_demo1;
    +---------------------+---------------------+
    | column1             | column2             |
    +---------------------+---------------------+
    | 2022-11-11 14:22:22 | 2022-11-11 15:22:22 |
    | 2022-11-13 21:54:06 | 2022-11-13 22:54:06 |
    +---------------------+---------------------+

-- CHAR测试
-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_char_demo1(
    column1 CHAR      COMMENT '默认长度字符串1',
    column2 CHAR(3)   COMMENT '固定字符串3',
    column3 CHAR(5)   COMMENT '固定字符串5');
-- 查询表结构
     DESC table_char_demo1;
    +---------+---------+------+-----+---------+-------+
    | Field   | Type    | Null | Key | Default | Extra |
    +---------+---------+------+-----+---------+-------+
    | column1 | char(1) | YES  |     | NULL    |       |
    | column2 | char(3) | YES  |     | NULL    |       |
    | column3 | char(5) | YES  |     | NULL    |       |
    +---------+---------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_char_demo1 VALUES('a','abc','abcde'),('我','我们的','开心每一天');
    INSERT INTO type_demo.table_char_demo1 VALUES('a','aa ','   de');
    SELECT * FROM table_char_demo1;
    +---------+-----------+-----------------+
    | column1 | column2   | column3         |
    +---------+-----------+-----------------+
    | a       | abc       | abcde           |
    | 我      | 我们的     | 开心每一天       |
    | a       | aa        | a  de           |
    +---------+-----------+-----------------+
-- 计算占用字节数
    SELECT LENGTH(column1),LENGTH(column2),LENGTH(column3) FROM table_char_demo1;
    +-----------------+-----------------+-----------------+
    | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) |
    +-----------------+-----------------+-----------------+
    |               1 |               3 |               5 |
    |               3 |               9 |              15 |
    |               1 |               2 |               5 |
    +-----------------+-----------------+-----------------+
    结论1：一个字母在CHAR保存是占用一个字节，汉字为3个字节（utf8mb3下）
    结论2：最后一行的第二个字段我们添加时，第三个为空格，但是没被体现和占用
    验证结论2：
        SELECT CONCAT('|',column2,'|') FROM table_char_demo1;
        +-------------------------+
        | CONCAT('|',column2,'|') |
        +-------------------------+
        | |abc|                   |
        | |我们的|                 |
        | |aa|                    |
        +-------------------------+
-- 总结：
    所以在CHAR里插入尾部空格则会被自动剔除，但是在前面插入空格则不会被剔除

-- VARCHAR测试：
-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_varchar_demo1(
    column1 VARCHAR(3)      COMMENT '可以添加长度3个字符',
    column2 VARCHAR(5)    COMMENT '可以添加长度5个字符');
-- 查询表结构
    DESC table_varchar_demo1;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | column1 | varchar(3) | YES  |     | NULL    |       |
    | column2 | varchar(5) | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_varchar_demo1 VALUES('abc','ab   '),('我在','在哪里  ');
    SELECT * FROM table_varchar_demo1;
    +---------+-------------+
    | column1 | column2     |
    +---------+-------------+
    | abc     | ab          |
    | 我在    | 在哪里      |
    +---------+-------------+
-- 查询长度和测试后面空格
    SELECT LENGTH(column1),LENGTH(column2) FROM table_varchar_demo1;
    +-----------------+-----------------+
    | LENGTH(column1) | LENGTH(column2) |
    +-----------------+-----------------+
    |               3 |               5 |
    |               6 |              11 |
    +-----------------+-----------------+
    SELECT CONCAT('|',column1,'|'),CONCAT('|',column2,'|') FROM table_varchar_demo1;
    +-------------------------+-------------------------+
    | CONCAT('|',column1,'|') | CONCAT('|',column2,'|') |
    +-------------------------+-------------------------+
    | |abc|                   | |ab   |                 |
    | |我在|                  | |在哪里  |               |
    +-------------------------+-------------------------+
    由上面可以看出统计字符时；一个字母系统按照1个字节存储，一个汉字按3字节存储（utf8mb3下）
    还要注意的是VARCHAR是可以存储尾部空格的
    还要注意的是在普通模式下我们存储的字符超过限制时MySQL会自动截取，并抛出警告；严格模式则报错
    INSERT INTO type_demo.table_varchar_demo1 VALUES('123456789','123456789');
    SELECT CONCAT('|',column1,'|'),CONCAT('|',column2,'|') FROM table_varchar_demo1;
    +-------------------------+-------------------------+
    | CONCAT('|',column1,'|') | CONCAT('|',column2,'|') |
    +-------------------------+-------------------------+
    | |123|                   | |12345|                 |
    +-------------------------+-------------------------+

-- 测试以TEXT为主（其它都是一样的）
-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_text_demo1(
    column1 TEXT          COMMENT '默认长度字符65535',
    column2 TEXT(254)   COMMENT '最大保存长度字符255',
    column3 TEXT(65536)   COMMENT '最大保存长度字符16777215'
    )CHARSET latin1;
    -- 说明：其实我们指定长度后；MySQL会根据当前指定的大小给我们设置合适的文本类型
-- 查询表结构
    DESC table_text_demo1;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | column1 | text       | YES  |     | NULL    |       |
    | column2 | tinytext   | YES  |     | NULL    |       |
    | column3 | mediumtext | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_text_demo1 VALUES('a','bb','ccc   ');
    INSERT INTO type_demo.table_text_demo1 VALUES('a','aa ','   de');
    SELECT * FROM table_text_demo1;
    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    | a       | bb      | ccc     |
    | a       | aa      |    de   |
    +---------+---------+---------+
-- 计算占用字节数
     SELECT LENGTH(column1),LENGTH(column2),LENGTH(column3) FROM table_text_demo1;
    +-----------------+-----------------+-----------------+
    | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) |
    +-----------------+-----------------+-----------------+
    |               1 |               2 |               6 |
    |               1 |               3 |               5 |
    +-----------------+-----------------+-----------------+
-- 注意：上面我以latin1字符集测试；若使用utf8mb3则需要进行一个计算

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_enum_demo1(
    column1 ENUM('男','女','secrecy')  COMMENT '定义3个元素枚举');
-- 查询表结构
    DESC table_enum_demo1;
    +---------+-----------------------------+------+-----+---------+-------+
    | Field   | Type                        | Null | Key | Default | Extra |
    +---------+-----------------------------+------+-----+---------+-------+
    | column1 | enum('男','女','secrecy')   | YES  |     | NULL    |       |
    +---------+-----------------------------+------+-----+---------+-------+
-- 添加数据
    -- 基本插入
    INSERT INTO type_demo.table_enum_demo1 VALUES('男'),('secrecy');
    -- 其它方式插入
    INSERT INTO type_demo.table_enum_demo1 VALUES(1),('3');
    SELECT * FROM table_enum_demo1;
    +---------+
    | column1 |
    +---------+
    | 男      |
    | secrecy |
    | 男      |
    | secrecy |
    +---------+
    -- 总结：在MySQL中，是可以自动转换数据格式的，比如 SELECT 1 + '1YES'; 得出 2
-- 最终存储的方式
    SELECT column1, column1+0 FROM table_enum_demo1;
    +---------+-----------+
    | column1 | column1+0 |
    +---------+-----------+
    | 男      |         1 |
    | secrecy |         3 |
    | 男      |         1 |
    | secrecy |         3 |
    +---------+-----------+
--其它查询
    SELECT column1, LENGTH(column1) FROM table_enum_demo1;
    +---------+-----------------+
    | column1 | LENGTH(column1) |
    +---------+-----------------+
    | 男      |               3 |
    | secrecy |               7 |
    | 男      |               3 |
    | secrecy |               7 |
    +---------+-----------------+
    因为在查询时显示的数据已经是被转换为具体的内容了

-- 创建表语句
    CREATE TABLE IF NOT EXISTS type_demo.table_set_demo1(
    phone SET('一加','小米','华为','苹果','三星','锤子','VIVO','OPPO') COMMENT '定义枚举');
-- 查询表结构
     DESC table_set_demo1;
    +-----+-----------------------------------------------------------+----+---+-------+-----+
    |Field|Type                                                       |Null|Key|Default|Extra|
    +-----+-----------------------------------------------------------+----+---+-------+-----+
    |phone|set('一加','小米','华为','苹果','三星','锤子','VIVO','OPPO') |YES |   |NULL   |     |
    +-----+-----------------------------------------------------------+----+---+-------+-----+
-- 添加数据
    INSERT INTO type_demo.table_set_demo1 VALUES('小米,华为'),('华为,三星,VIVO');
    SELECT * FROM table_set_demo1;
    +--------------------+
    | phone              |
    +--------------------+
    | 小米,华为           |
    | 华为,三星,VIVO      |
    +--------------------+
-- 说明
    其实我们在创建表的时候，有个规律，元素达到8个就为一字节了，元素达到16个就为两字节了，那这么做的目录是
    存储时以二进制的方式存储
    比如我们查看它具体插入的是什么
    SELECT phone + 0 FROM table_set_demo1;
    +-----------+
    | phone + 0 |
    +-----------+
    |         6 |
    |        84 |
    +-----------+
    这给我们打印出具体的数值了，其实我们把这个翻译出二进制就可以更好表现出来
    比如：84
    转换：0        1        0        1        0        1        0        0
    颠倒：0        0        1        0        1        0        1        0    
    对应：一加  小米    华为    苹果    三星     锤子    VIVO    OPPO
    结论：计算出来的1则代表具体的值；所以MySQL的SET类型是以二进制方式存储的，一个bit位代表一个元素
         所以我们在插入具体的数值也是可以的，比如255（对应二进制11111111）正好选中全部元素
    INSERT INTO type_demo.table_set_demo1 VALUES(255);
    +-----------------------------------------------------+
    | phone                                               |
    +-----------------------------------------------------+
    | 小米,华为                                            |
    | 华为,三星,VIVO                                       |
    | 一加,小米,华为,苹果,三星,锤子,VIVO,OPPO               |
    +-----------------------------------------------------+

-- BINARY测试
-- 创建表语句
    CREATE TABLE IF NOT EXISTS `type_demo`.`table_binary_demo1`(
        column1 BINARY         COMMENT '默认长度1个字节',
        column2 BINARY(2)     COMMENT '长度为2个字节',
        column3 BINARY(3)     COMMENT '长度为3个字节'
    ) CHARSET utf8mb3;
-- 查询表结构
    DESC `type_demo`.`table_binary_demo1`；
    +---------+-----------+------+-----+---------+-------+
    | Field   | Type      | Null | Key | Default | Extra |
    +---------+-----------+------+-----+---------+-------+
    | column1 | binary(1) | YES  |     | NULL    |       |
    | column2 | binary(2) | YES  |     | NULL    |       |
    | column3 | binary(3) | YES  |     | NULL    |       |
    +---------+-----------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_binary_demo1 VALUES('a','bc','def'),('1','23','我');
    SELECT * FROM type_demo.table_binary_demo1;
    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    | a       | bc      | def     |
    | 1       | 23      | 我      |
    +---------+---------+---------+
    -- 注：为什么可以插入汉字，因为binary为3字节正好可以存储一个utfmb3的汉字，若2字节可以存储一个个gbk汉字
-- 会出现问题的数据插入
    INSERT INTO type_demo.table_binary_demo1 VALUES('我','我们','我们的');
    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    | æ       | æˆ      | 我      |
    +---------+---------+---------+
    -- 这个查询就会出现乱码，因为一个字节不能存储3个字节的一个汉字，所以column1就截取一个字节存储，
    -- column3可以存储三个字节，所以就直接把‘我’存储了，后面的则丢弃（在MySQL严格模式则报错）
-- 比较说明
    INSERT INTO type_demo.table_binary_demo1 VALUES('a','b','c');
    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    | a       | b       | c       |
    +---------+---------+---------+
    SELECT column1 = 'a' , column2 = 'b' , column3 = 'c' FROM table_binary_demo1;
    +---------------+---------------+---------------+
    | column1 = 'a' | column2 = 'b' | column3 = 'c' |
    +---------------+---------------+---------------+
    |             1 |             0 |             0 |
    +---------------+---------------+---------------+
    为什么 column1 = 'a' 比较为true，其它则都是0；看似值都一样，其实未存满的字节里面使用了'\0'
    SELECT column1 = 'a' , column2 = 'b\0' , column3 = 'c\0\0' FROM table_binary_demo1;
    +---------------+-----------------+-------------------+
    | column1 = 'a' | column2 = 'b\0' | column3 = 'c\0\0' |
    +---------------+-----------------+-------------------+
    |             1 |               1 |                 1 |
    +---------------+-----------------+-------------------+
    SELECT HEX(column1) , HEX(column2) , HEX(column3) FROM table_binary_demo1;
    +--------------+--------------+--------------+
    | HEX(column1) | HEX(column2) | HEX(column3) |
    +--------------+--------------+--------------+
    | 61           | 6200         | 630000       |
    +--------------+--------------+--------------+
-- 长度说明
    INSERT INTO type_demo.table_binary_demo1 VALUES('1','1','1');
    SELECT LENGTH(column1) , LENGTH(column2) , LENGTH(column3) FROM table_binary_demo1;
    +-----------------+-----------------+-----------------+
    | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) |
    +-----------------+-----------------+-----------------+
    |               1 |               2 |               3 |
    +-----------------+-----------------+-----------------+
    -- 长度不足将在右边填充'\0'以补齐指定长度；

-- VARBINARY测试
-- 创建表语句
    CREATE TABLE IF NOT EXISTS `type_demo`.`table_varbinary_demo1`(
        column1 VARBINARY(1)         COMMENT '设置长度1个字节',
        column2 VARBINARY(2)         COMMENT '设置长度2个字节',
        column3 VARBINARY(3)         COMMENT '设置长度3个字节'
    ) CHARSET utf8mb3;
-- 查询表结构
    DESC `type_demo`.`table_varbinary_demo1`;
    +---------+--------------+------+-----+---------+-------+
    | Field   | Type         | Null | Key | Default | Extra |
    +---------+--------------+------+-----+---------+-------+
    | column1 | varbinary(1) | YES  |     | NULL    |       |
    | column2 | varbinary(2) | YES  |     | NULL    |       |
    | column3 | varbinary(3) | YES  |     | NULL    |       |
    +---------+--------------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_varbinary_demo1 VALUES('a','bc','def'),('1','23','我');
    SELECT * FROM type_demo.table_varbinary_demo1;
    +---------+---------+---------+
    | column1 | column2 | column3 |
    +---------+---------+---------+
    | a       | bc      | def     |
    | 1       | 23      | 我      |
    +---------+---------+---------+
-- 长度说明
    INSERT INTO type_demo.table_varbinary_demo1 VALUES('1','1','1');
    SELECT LENGTH(column1) , LENGTH(column2) , LENGTH(column3) FROM table_varbinary_demo1;
    +-----------------+-----------------+-----------------+
    | LENGTH(column1) | LENGTH(column2) | LENGTH(column3) |
    +-----------------+-----------------+-----------------+
    |               1 |               1 |               1 |
    +-----------------+-----------------+-----------------+
    -- 由此证明；VARBINARY是可变长度，变成，只保存需要保存的数据，空出来的空间则不再需要；
    -- 但是需要使用1~2个字节记录这个数据的长度，255以为1个字节，255~65535则需要2个字节

-- JSON测试
-- 创建表语句
    CREATE TABLE IF NOT EXISTS `type_demo`.`table_json_demo1`(
        column1 JSON         COMMENT '创建一个JSON类型的字段'
    ) CHARSET utf8mb3;
-- 查询表结构
    DESC `type_demo`.`table_json_demo1`；
    +---------+------+------+-----+---------+-------+
    | Field   | Type | Null | Key | Default | Extra |
    +---------+------+------+-----+---------+-------+
    | column1 | json | YES  |     | NULL    |       |
    +---------+------+------+-----+---------+-------+
-- 添加数据
    INSERT INTO type_demo.table_json_demo1 VALUES
    ('{"name":"张三","age":22}'),('{"name":"李四","age":22}'),('{"name":"麻子","age":25}');
    SELECT * FROM type_demo.table_json_demo1;
    +-------------------------------+
    | column1                       |
    +-------------------------------+
    | {"age": 22, "name": "张三"}   |
    | {"age": 22, "name": "李四"}   |
    | {"age": 25, "name": "麻子"}   |
    +-------------------------------+
-- 检索指定字段
    SELECT column1 -> '$.name' '姓名', column1 -> '$.age' '年龄'  FROM table_json_demo1;
    +----------+--------+
    | 姓名     | 年龄   |
    +----------+--------+
    | "张三"   | 22     |
    | "李四"   | 22     |
    | "麻子"   | 25     |
    +----------+--------+
-- 更新指定字段
    SELECT *  FROM table_json_demo1 WHERE column1 -> '$.name' = "张三";
    +-------------------------------+
    | column1                       |
    +-------------------------------+
    | {"age": 22, "name": "张三"}   |
    +-------------------------------+