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