UDF 是较为早的版本中引进的特性 ，JSON类型是在5.7就已经引进了，到MySQL8.0中得到了增强，本文以8.0来详细和举例说明这两个特性。
UDF
用户自定义函数（user defined function，UDF）是一种对MYSQL扩展的途径，其用法与内置函数相同。自定义函数的必要两个条件：参数、返回值；函数可以返回任意类型的值，同样可以接受这些类型的参数。
# 创建自定义函数规范
CREATE FUNCTION function_name
RETURNS {STRING|INTEGER|REAL|DECIMAL}
routine_body
关于函数体有几个注意点：
- 函数体由合法的SQL语句构成
- 函数体可以是简单的select或insert语句；
- 函数体如果为复合构造，则使用begin...end语句；
- 复合构造可以包含声明，循环，控制结构等；
- 函数是不能重名的
# 删除函数
DROP FUNCTION [IF EXISTS] function_name
案例1，自定义一个函数，用来格式化时间，提供给其他人可使用该函数
# 创建我的时间格式化
CREATE FUNCTION my_data_format()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(),'%Y年%m%d日 %H时%i分%s秒')
# 执行会报错，意思是你的函数里没有描述sql语句，需要设置 log_bin_trust_function_creators（信任仓作者）
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
# 可以在my.cnf里的[mysqld]下配置（注意配置中的_改为-），也可以在全局进行设置，之后执行创建
set global log_bin_trust_function_creators=1;
# 执行查询
SELECT my_data_format();
my_data_format()
--------------------------------
2022年0519日 21时04分29秒
案列2，得到两个数的和
CREATE FUNCTION my_add(num INT,num1 INT)
RETURNS INT
RETURN num + num1
SELECT my_add(1,2)
my_add(1,2)
-------------
3
案例3，插入一条数据，并返回插入数据的ID
# 创建表
CREATE TABLE `t` (
`id` int NOT NULL AUTO_INCREMENT,
`att` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
# 修改结束语句标识
DELIMITER //
# 创建函数
CREATE FUNCTION my_insert(attr VARCHAR(100))
RETURNS INT
BEGIN
INSERT INTO t(att) VALUES(attr);
RETURN LAST_INSERT_ID();
END
# 修改为以前结束语句标识
DELIMITER ;
# 执行
select my_insert('name');
JSON
创建一个带有JSON类型的表
CREATE TABLE json_t(
id INT NOT NULL AUTO_INCREMENT,
content json NOT NULL,
PRIMARY KEY (`id`)
);
JSON 类型的函数说明：
- JSON_ARRAY() 返回JSON数组
- JSON_OBJECT() 返回JSON对象
- JSON_ARRAY_INSERT() 将数据插入到JSON数组中，不替换现有值
- JSON_INSERT() 将数据插入到JSON文档中，不替换现有值
- JSON_ARRAY_APPEND() 值附加到JSON文档中指定数组的末尾并返回值
- JSON_SET() 替换现有的值，并增加不存储在的值
- JSON_REPLACE() 仅替换现有的值
- JSON_REMOVE() 删除指定JSON值
- JSON_EXTRACT() 从JSON中返回数据
- JSON_PRETTY() 格式化JSON并打印
- JSON_CONTAINS_PATH() 返回json文档是否包含指定的路径
- JSON_CONTAINS() 返回查找数据是否在指定的json文档中
- JSON_LENGTH() 返回json文档的长度
# 插入数据
INSERT INTO json_t(content) VALUES('{
"min_position": 6,
"has_more_items": true,
"items_html": "Bike",
"new_latent_count": 1,
"data": {
"length": 27,
"text": "Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat."
},
"numericalArray": [
32,
29,
26,
28,
33
],
"StringArray": [
"Nitrogen",
"Carbon",
"Oxygen",
"Carbon"
],
"multipleTypesArray": 5,
"objArray": [
{
"class": "lower",
"age": 1
},
{
"class": "middle",
"age": 4
},
{
"class": "middle",
"age": 0
},
{
"class": "middle",
"age": 5
},
{
"class": "upper",
"age": 8
}
]
}')
# 格式化json输出并查看
SELECT json_pretty(content) FROM json_t;
# json_object 和 json_array 组合使用，这两个支持并不好太恶心了
SELECT json_pretty(
json_object(
"min_position",6,
"has_more_items",TRUE,
"items_html","Bike",
"new_latent_count",1,
"data",json_object(
"length",27,
"text","Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat."
),
"numericalArray",json_array(
32,
29,
26,
28,
33
),
"StringArray",json_array(
"Nitrogen",
"Carbon",
"Oxygen",
"Carbon"
),
"multipleTypesArray",5,
"objArray",json_array(
json_object(
"class","lower",
"age",1
),
json_object(
"class","middle",
"age",4
),
json_object(
"class","middle",
"age",0
),
json_object(
"class","middle",
"age",5
),
json_object(
"class","upper",
"age",8
)
)
)
)
# 预览JSON_ARRAY_INSERT效果，不会替换任何值，会插入一个新值
SELECT JSON_ARRAY_INSERT(content,"$.objArray[0]",json_object(
"age", 3213,
"class", "lower"
)) FROM json_t
# 给数组添加一个新对象，并修改json文档
UPDATE json_t SET content = JSON_ARRAY_INSERT(content,"$.objArray[0]",json_object(
"age", 3213,
"class", "lower"
)) WHERE id = 1
#预览json_insert效果，不会替换文档任何属性的值，只会新增
SELECT json_insert(content,"$.color","red") FROM json_t
# 修改文档对象，添加一个新的属性，不能直接是json字符串的添加
UPDATE json_t SET content = json_insert(content,"$.colors",json_array(
json_object(
"name","红色",
"color","red"
),
json_object(
"name","黄色",
"color","yellow"
)
)) WHERE id = 1
# 如果你是字符串的方式，他会把你的属性的值当初一个字符串，不会当成数组套对象
UPDATE json_t SET content = json_insert(content,"$.color_n",'[
{
"name":"红色112",
"color":"red"
},
{
"name":"黄色33",
"color":"yellow"
}
]') WHERE id = 1
# 预览JSON_ARRAY_APPEND()，会把添加的位置变为一个数组，包括属性的的值
SET @jsonA = json_array('a','b','c','f','g')
SELECT json_array_append(@jsonA,'$[1]','bb')
SET @jsonO = json_object('name','zs','age','18','fatherName','ls')
SELECT json_array_append(@jsonO,'$.fatherName','ww')
#预览json_set，如果JSON有属性则修改，无属性就添加
SELECT json_set(@jsonO,'$.age',23,'$.sex','男')
#预览 json_replace，如果有相同属性，则替换里面的值，没有则不作任何处理
SELECT json_replace(@jsonO,'$.age','18','$.sex','2');
# 预览 json_remove，删除JSON文档的属性
SELECT json_remove(@jsonO,'$.age')
# 预览json_extract，查看JSON属性值的两种方式，第二种只在MySQL8.x有
SELECT json_extract(@jsonO,'$.name')
SELECT content ->> "$.data.text" FROM json_t
# 预览json_lemgth，该方如果是数组返回元素个数，如果是对象返回属性个数
SELECT json_length(content,"$.objArray") FROM json_t
# 预览 json_contains_path，该函数查找JSON文档中是否包含该属性，返回0和1，one 是多个属性，至少有一个，all 表示，多个属性都得有
SELECT json_contains_path(content,'one',"$.colors") FROM json_t
# 预览 json_contains，该函数查找JSON文档，判断该属性的值是否为字符串6
SELECT json_contains(content,'6',"$.min_position") FROM json_t
网友评论