四、mysql数据类型

目录

一、数据类型

1、INT 类型

2、数字类型

3、字符串类型

4、日期类型

5、json类型

6、枚举类型

---

一、数据类型

1、INT 类型

属性：

• unsigned/signed：是否有符号
• zerofill：显示属性、值不做任何修改
• auto_increment：自增、每张表一个、必须是索引的一部分

2、数字类型

1. float：单精度
2. double：双精度
3. declmal：高精度（建议使用）declmal(M,D):M表示显示M位数，D表示位于小数点后面

3、字符串类型

1. char(N):
2. varchar(N)

4、日期类型

1. datetime：日期+时间 占用8个字节
2. date：日期 占用3个字节
3. timestamp：日期加时间 占用4个字节
4. year：年份 占用1个字节
5. time： 时间 占用3个字节

5、json类型

1. 5.7版本之后支持

$ 符号代表JSON路径表达式的根元素

示例：
CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

(root@localhost) [test]> INSERT INTO test(data) VALUES ('{"name": "张三", "age": 25, "address": {"city": "北京", "street": "朝阳区"}}');

Query OK, 1 row affected (0.01 sec)
(root@localhost) [test]> select * from test;
+----+-------------------------------------------------------------------------------------+
| id | data                                                                                |
+----+-------------------------------------------------------------------------------------+
|  1 | {"age": 25, "name": "张三", "address": {"city": "北京", "street": "朝阳区"}}        |
+----+-------------------------------------------------------------------------------------+

一、查询数据：使用各种函数
1、使用json_extract()函数   或者 -> 进行查询
(root@localhost) [test]> select json_extract(data,'$.name') from test;
+-----------------------------+
| json_extract(data,'$.name') |
+-----------------------------+
| "张三"                      |
+-----------------------------+

(root@localhost) [test]> select data -> '$.name' from test;
+------------------+
| data -> '$.name' |
+------------------+
| "张三"           |
+------------------+

(root@localhost) [test]> select json_extract(data,'$.address.city') from test;
+-------------------------------------+
| json_extract(data,'$.address.city') |
+-------------------------------------+
| "北京"                              |
+-------------------------------------+

2、使用 JSON_UNQUOTE()：此函数用于去除JSON值的双引号。
(root@localhost) [test]> select json_unquote(data->'$.address.city') from test;
+--------------------------------------+
| json_unquote(data->'$.address.city') |
+--------------------------------------+
| 北京                                 |
+--------------------------------------+

3、->>：这是 -> 和 JSON_UNQUOTE() 的简写，用于提取JSON值并去除双引号
(root@localhost) [test]> select data ->> '$.name' from test;
+-------------------+
| data ->> '$.name' |
+-------------------+
| 张三              |
+-------------------+

4、JSON_OBJECT()：创建一个JSON对象。
语法：SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2')
(root@localhost) [test]> select json_object('name',data ->>'$.name','city',data ->>'$.address.city')output from test;
+--------------------------------------+
| output                               |
+--------------------------------------+
| {"city": "北京", "name": "张三"}     |
+--------------------------------------+

5、JSON_LENGTH()：此函数用于计算JSON数组或对象的长度。
(root@localhost) [test]> INSERT INTO test (data) VALUES ('{"hobbies": ["basketball", "football", "reading"]}');
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> select json_length(data,'$.hobbies') from test where id =2;
+-------------------------------+
| json_length(data,'$.hobbies') |
+-------------------------------+
|                             3 |
(root@localhost) [test]> select json_length(data,'$.address') from test where id =1;
+-------------------------------+
| json_length(data,'$.address') |
+-------------------------------+
|                             2 |

6、查询数组中的数据
(root@localhost) [test]> select data ->> '$.hobbies[1]' from test where id =2;
+-------------------------+
| data ->> '$.hobbies[1]' |
+-------------------------+
| football                |
+-------------------------+

二、修改json数据
常用函数：json_set()修改、json_replace()替换

1、修改 json_set()
(root@localhost) [test]> select * from  test;
+----+-------------------------------------------------------------------------------------+
| id | data                                                                                |
+----+-------------------------------------------------------------------------------------+
|  1 | {"age": 25, "name": "张三", "address": {"city": "北京", "street": "朝阳区"}}        |
|  2 | {"hobbies": ["basketball", "football", "reading"]}                                  |
+----+-------------------------------------------------------------------------------------+

(root@localhost) [test]> update test set data = json_set(data,'$.age',30) where id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

(root@localhost) [test]> select * from  test;
+----+-------------------------------------------------------------------------------------+
| id | data                                                                                |
+----+-------------------------------------------------------------------------------------+
|  1 | {"age": 30, "name": "张三", "address": {"city": "北京", "street": "朝阳区"}}        |
|  2 | {"hobbies": ["basketball", "football", "reading"]}                                  |
+----+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

2、替换json_replace()
(root@localhost) [test]> update test set data = json_replace(data,'$.address.city','上海') where id= 1;
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> select * from  test;
+----+-------------------------------------------------------------------------------------+
| id | data                                                                                |
+----+-------------------------------------------------------------------------------------+
|  1 | {"age": 30, "name": "张三", "address": {"city": "上海", "street": "朝阳区"}}        |
|  2 | {"hobbies": ["basketball", "football", "reading"]}                                  |
+----+-------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

三、删除

四、创建函数索引
虽然JSON字段不支持传统的索引，但可以使用虚拟列和生成列来创建索引。例如，为"name"键创建索引：
ALTER TABLE example ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (data->'$.name') VIRTUAL;
CREATE INDEX idx_example_name ON example (name)

6、枚举类型

1. a enum(a,b)：只能插入a或者b，插入其他的会报错