MySQL数据库数据类型_mysql数据库类型

-- 以下是关于MySQL中JSON数据类型的更多示例：
 
-- 创建表
 
   CREATE TABLE `tableName` (`col1` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `col2` VARCHAR(45), `data` JSON DEFAULT NULL);
   
   INSERT INTO `tableName`(`col1`, `col2`) VALUES (NULL, 'test data'), (NULL, 'other test data');
   SELECT * FROM `tableName`;
   +----+--------+---------------------+
   | col1| col2   | data                |
   +----+--------+---------------------+
   |    1| test data| NULL               |
   |    2| other test data| NULL           |
   +----+--------+---------------------+
   
   
   UPDATE `tableName` SET `data`=CAST('[ { "a":"A", "b":"B"},{ "c":"C"} ]' AS JSON) WHERE `col1`='1';
   SELECT * FROM `tableName`;
   +----+--------+---------------------+
   | col1| col2   | data                |
   +----+--------+---------------------+
   |    1| test data| [ {"a":"A","b":"B"},{"c":"C"} ] |
   |    2| other test data| NULL             |
   +----+--------+---------------------+
   
   DELETE FROM `tableName` WHERE `col1`='2';
   SELECT * FROM `tableName`;
   +----+--------+---------------------+
   | col1| col2   | data                |
   +----+--------+---------------------+
   |    1| test data| [ {"a":"A","b":"B"},{"c":"C"} ]      |
   +----+--------+---------------------+
   ```
 
 -- 获取指定属性值
 
   select CAST(JSON_UNQUOTE(JSON_EXTRACT("{\"a\":\"A\", \"b\":\"B\", \"c\":\"C\"}", "$.")) as CHAR) from dual limit 1;
   // output: A
   
   select CAST(JSON_UNQUOTE(JSON_EXTRACT("{\"a\":\"A\", \"b\":\"B\", \"c\":\"C\"}", "$.d")) as CHAR) from dual limit 1;
   // output: NULL
   
   select CAST(JSON_UNQUOTE(JSON_SEARCH("{\"a\":\"A\", \"b\":\"B\", \"c\":\"C\"}", "one", "all", "$[*].*"))) as CHAR from dual limit 1;
   // output: B
   
   select CAST(JSON_UNQUOTE(JSON_SEARCH("{\"a\":\"A\", \"b\":\"B\", \"c\":\"C\"}", "one", "all", "$[?(@.c)].*"))) as CHAR from dual limit 1;
   // output: C
 
 
-- 修改指定属性值
 
 
   create or replace function set_key_val() returns string soname 'libset_key_value.so';
   
   -- set a new value to the existing object with specific path
   update t1 set jdoc = set_key_val(jdoc,'$["fruit"]', '"apple"') where k = 1 and v = 9876543210;
   delete from t1 where k in (1,2,3,4,5);
   insert into t1 values ('1','1000000000',cast('{"animal":"cat", "color":"blue", "num":1}' as JSON));
   insert into t1 values ('2','1000000001',cast('{"animal":"dog", "food":"meat", "price":12.50}' as JSON));
   insert into t1 values ('3','1000000002',cast('{"toy":"ball", "owner":"john", "num":3}' as JSON));
   insert into t1 values ('4','1000000003',cast('{"animal":"snake", "food":"mice", "length":2}"' as JSON));
   insert into t1 values ('5','1000000004',cast('{"vehicle":"car", "speed":"fast", "manufacturer":"Honda" }' as JSON));
   commit;
   
   select * from t1;
   -+---------------+------+---------------------------------------------------------+
   |        k       |   v   |                          jdoc                            |
   +---------------+------+---------------------------------------------------------+
   | 1              | 1000000000 | {"animal":"cat", "color":"blue", "num":1,"fruit":"apple"}         |
   | 2              | 1000000001 | {"animal":"dog", "food":"meat", "price":12.50}              |
   | 3              | 1000000002 | {"toy":"ball", "owner":"john", "num":3}                         |
   | 4              | 1000000003 | {"animal":"snake", "food":"mice", "length":2}                     |
   | 5              | 1000000004 | {"vehicle":"car", "speed":"fast", "manufacturer":"Honda"}         |
   +---------------+------+---------------------------------------------------------+
   
   -- add a new property at root level of an existing document
   update t1 set jdoc = set_key_val(jdoc,'$','"hello world!"') where k between 1 and 5;
   commit;
   
   select * from t1;
   -+---------------+------+----------------------------------------------------------------------------------+
   |        k       |   v   |                          jdoc                                                         |
   +---------------+------+----------------------------------------------------------------------------------+
   | 1              | 1000000000 | {"hello world!", "animal":"cat", "color":"blue", "num":1,"fruit":"apple"}         |
   | 2              | 1000000001 | {"hello world!", "animal":"dog", "food":"meat", "price":12.50}              |
   | 3              | 1000000002 | {"hello world!", "toy":"ball", "owner":"john", "num":3}                         |
   | 4              | 1000000003 | {"hello world!", "animal":"snake", "food":"mice", "length":2}                     |
   | 5              | 1000000004 | {"hello world!", "vehicle":"car", "speed":"fast", "manufacturer":"Honda"}         |
   +---------------+------+----------------------------------------------------------------------------------+
   ```
 
-- 使用JSONPATH查询
 
 
   select * from employees where department = $['department']
   select * from employees where department like '%Json%'
   select * from employees where department is Json
   select * from employees where department contains Json
   select * from employees where department matches Json
 
 
-- 使用JSONPATH进行查询
 
 
   select * from employees where department @> department like '{'department': ['department']
   select * from employees where department @> department contains 'department'
  
   select * from employees where department matches '[]'
  

MySQL的JSON数据类型允许您将复杂的数据结构存储在单个字段中，从而简化您的数据模型。您可以使用JSON数据类型来存储嵌套对象、数组和混合数据类型。此外，MySQL还提供了许多操作JSON数据的内置函数，如`JSON_EXTRACT()`、`JSON_OBJECT()`等。

--这个例子中，我们创建了一个名为`users`的表，它有三个字段：`id`（主键）、`name`（不能为空）和`info`（默认为空JSON对象）
create table users (
  id int auto_increment primary key,
  name varchar(256) not null,
  info json not null default "{}",
  created timestamp default current_timestamp on update current_timestamp);

insert into users values (null, "John Doe", '{"age":30}');
select * from users where info->"$.age" = 30;
 
-- 第一条语句将一个包含年龄信息的JSON对象插入到`info`字段中。
-- 第二条语句使用JSON路径表达式查找所有年龄在30岁的用户。

数据类型的属性