数据库内置函基础解析：提升数据处理效率的秘密武器

数据库中的内置函数是预先定义好的函数，能够在 SQL 查询中直接使用，用于各种数据操作和处理。

1. 锁相关

锁相关函数用于管理数据库锁，以确保数据的一致性和完整性。

示例：

-- 获取当前会话的所有锁信息
SELECT * FROM information_schema.innodb_locks;

2. JSON函数

JSON函数用于处理 JSON 数据格式，可以将 JSON 数据解析、生成、查询和修改。

示例：

-- 从JSON对象中提取数据
SELECT JSON_EXTRACT('{"name": "Joe", "age": 25}', '$.name') AS name;

3. 窗口函数

窗口函数允许用户在一个查询中对结果集进行分区和排序操作，常用于分析性的计算。

示例：

-- 计算每个部门中员工的工资排名
SELECT name, dept, salary, RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank
FROM employee;

4. 时间日期函数

时间日期函数用于处理日期和时间数据，支持日期的格式化、计算等操作。

示例：

-- 获取当前日期和时间
SELECT NOW();

-- 计算日期差
SELECT DATEDIFF('2023-12-31', '2024-06-03') AS days_difference;

5. 其它函数

其它函数包括字符串处理函数、数学函数、聚合函数等。

示例：

-- 字符串函数
SELECT CONCAT('Hello', ' ', 'World') AS greeting;

-- 数学函数
SELECT ROUND(123.456, 2) AS rounded_number;

-- 聚合函数
SELECT AVG(salary) AS average_salary FROM employee;

这些内置函数提供了强大的数据处理能力，帮助用户在数据库中进行复杂的数据操作和分析。每个函数都有特定的语法和用法，掌握这些函数能够显著提高数据库操作的效率和灵活性。

锁函数

• GET_LOCK(value, timeout) 函数使用字符串 value 给定的名字获取锁，持续 timeout 秒。

  • 如果 GET_LOCK 成功获取锁，则返回 1，如果获取锁超时，则返回 0，如果发生错误，则返回 NULL。

  • 示例：

    SELECT GET_LOCK('my_lock', 10); -- 尝试在10秒内获取名为'my_lock'的锁

• RELEASE_LOCK(value) 函数将以 value 命名的锁解除。

  • 如果 RELEASE_LOCK 解除成功，则返回 1，如果线程还没有创建锁，则返回 0，如果以 value 命名的锁不存在，则返回 NULL。

  • 示例：

    SELECT RELEASE_LOCK('my_lock'); -- 解除名为'my_lock'的锁

• IS_FREE_LOCK(value) 函数判断以 value 命名的锁是否可以被使用。

  • 如果 IS_FREE_LOCK 判断锁可以被使用，则返回 1，如果不能使用，也就是说正在被使用，则返回 0，如果发生错误，则返回 NULL。

  • 示例：

    SELECT IS_FREE_LOCK('my_lock'); -- 判断名为'my_lock'的锁是否可用

• IS_USED_LOCK(value) 函数判断以 value 命名的锁是否正在被使用，如果正在被使用，则返回使用该锁的数据库连接 ID，否则返回 NULL。

  • 示例：

    SELECT IS_USED_LOCK('my_lock'); -- 判断名为'my_lock'的锁是否正在被使用

这些锁函数可以用于管理和控制对资源的访问，确保数据的一致性和完整性。每个函数都有特定的用途和返回值，正确理解和使用这些函数有助于在数据库操作中避免资源竞争和数据冲突。

JSON

• JSON_CONTAINS(json_doc, value) 函数查询 JSON 类型的字段中是否包含 value 数据。如果包含则返回 1，否则返回 0。

  • 示例：

    SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '1', '$.a'); -- 返回 1

• JSON_PRETTY(json_doc) 函数以优雅的格式显示 JSON 数据。

  • 示例：

    SELECT JSON_PRETTY('{"a":1,"b":{"c":2,"d":3}}'); 
    -- 返回
    -- {
    --   "a": 1,
    --   "b": {
    --     "c": 2,
    --     "d": 3
    --   }
    -- }

• JSON_SEARCH(json_doc, one_or_all, value[, path]) 函数在 JSON 类型的字段中查找字符串 value。如果找到 value 值，则返回路径数据。

  • 示例：

    SELECT JSON_SEARCH('{"a": 1, "b": {"c": 2, "d": 3}}', 'one', '2'); -- 返回 "$.b.c"

• JSON_DEPTH(json_doc) 函数返回 JSON 数据的最大深度。

  • 示例：

    SELECT JSON_DEPTH('{"a":1,"b":{"c":2,"d":3}}'); -- 返回 2

• JSON_LENGTH(json_doc[, path]) 函数返回 JSON 数据的长度。

  • 示例：

    SELECT JSON_LENGTH('{"a": 1, "b": [1, 2, 3]}'); -- 返回 2
    SELECT JSON_LENGTH('{"a": 1, "b": [1, 2, 3]}', '$.b'); -- 返回 3

• JSON_KEYS(json_doc[, path]) 函数返回 JSON 数据中顶层 key 组成的 JSON 数组。

  • 示例：

    SELECT JSON_KEYS('{"a": 1, "b": {"c": 2, "d": 3}}'); -- 返回 '["a", "b"]'

• JSON_INSERT(json_doc, path, val[, path, val] ...) 函数用于向 JSON 数据中插入数据。

  • 示例：

    SELECT JSON_INSERT('{"a": 1}', '$.b', 2); -- 返回 '{"a": 1, "b": 2}'

• JSON_REMOVE(json_doc, path[, path] ...) 函数用于移除 JSON 数据中指定 key 的数据。

  • 示例：

    SELECT JSON_REMOVE('{"a": 1, "b": 2}', '$.a'); -- 返回 '{"b": 2}'

• JSON_REPLACE(json_doc, path, val[, path, val] ...) 函数用于更新 JSON 数据中指定 key 的数据。

  • 示例：

    SELECT JSON_REPLACE('{"a": 1, "b": 2}', '$.a', 3); -- 返回 '{"a": 3, "b": 2}'

• JSON_SET(json_doc, path, val[, path, val] ...) 函数用于向 JSON 数据中插入或更新数据。

  • 示例：

    SELECT JSON_SET('{"a": 1}', '$.b', 2); -- 返回 '{"a": 1, "b": 2}'

• JSON_TYPE(json_val) 函数用于返回 JSON 数据的 JSON 类型。

  • 示例：

    SELECT JSON_TYPE('{"a": 1}'); -- 返回 'OBJECT'

• JSON_VALID(value) 函数用于判断 value 的值是否是有效的 JSON 数据，如果是，则返回 1，否则返回 0，如果 value 的值为 NULL，则返回 NULL。

  • 示例：

    SELECT JSON_VALID('{"a": 1}'); -- 返回 1
    SELECT JSON_VALID('invalid json'); -- 返回 0

以上 JSON 函数可以有效地操作和查询 JSON 数据，使得在 MySQL 中处理 JSON 数据更加方便和灵活。

function

• FIRST_VALUE(expr)函数返回第一个expr的值。

  • FIRST_VALUE 是一个窗口函数，它返回在窗口帧内排序的第一个值。例如，在一个按时间排序的销售记录表中，FIRST_VALUE 可以用来获取每个销售员的第一笔销售金额。

  • 示例：

    SELECT 
      employee, 
      sale_date, 
      sale_amount,
      FIRST_VALUE(sale_amount) OVER (PARTITION BY employee ORDER BY sale_date) AS first_sale_amount
    FROM 
      sales;

• LAST_VALUE(expr)函数返回最后一个expr的值。

  • LAST_VALUE 是一个窗口函数，它返回在窗口帧内排序的最后一个值。例如，在一个按时间排序的销售记录表中，LAST_VALUE 可以用来获取每个销售员的最后一笔销售金额。

  • 示例：

    SELECT 
      employee, 
      sale_date, 
      sale_amount,
      LAST_VALUE(sale_amount) OVER (PARTITION BY employee ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sale_amount
    FROM 
      sales;

以上两个函数在 SQL 查询中用于计算窗口内的第一个和最后一个值，可以帮助我们进行复杂的分析和数据处理。

• ROW_NUMBER()函数能够对数据中的序号进行顺序显示。

  • ROW_NUMBER 是一个窗口函数，用于返回窗口内每一行的唯一序号，从1开始递增。例如：

    SELECT 
      employee, 
      sale_amount,
      ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS row_num
    FROM 
      sales;

  • 在上述示例中，每个部门内的销售记录会根据销售金额降序排列，并为每条记录分配一个唯一的行号。

• 使用RANK()函数能够对序号进行并列排序，并且会跳过重复的序号。

  • RANK 是一个窗口函数，用于返回窗口内每一行的排名，如果有相同的值，则赋予相同的排名，并跳过重复的序号。例如：

    SELECT 
      employee, 
      sale_amount,
      RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS rank
    FROM 
      sales;

  • 在上述示例中，如果两个销售记录的金额相同，它们会获得相同的排名，而下一个排名会跳过一个序号。

• DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号。

  • DENSE_RANK 是一个窗口函数，用于返回窗口内每一行的排名，如果有相同的值，则赋予相同的排名，但不会跳过重复的序号。例如：

    SELECT 
      employee, 
      sale_amount,
      DENSE_RANK() OVER (PARTITION BY department ORDER BY sale_amount DESC) AS dense_rank
    FROM 
      sales;

  • 在上述示例中，如果两个销售记录的金额相同，它们会获得相同的排名，而下一个排名会紧接其后。

举例1

create function individual_income_tax(salary decimal(12, 4)) 
returns decimal(12, 4)
deterministic
begin
    -- 实现逻辑
end;

函数定义中的各部分解释

1. create function individual_income_tax(salary decimal(12, 4)):

   • create function 是创建函数的关键字。
   • individual_income_tax 是函数名称。
   • salary decimal(12, 4) 定义了函数的输入参数，名称为 salary，类型为 decimal(12, 4)。

2. returns decimal(12, 4):

   • returns 关键字定义了函数的返回类型，这里返回一个 decimal(12, 4) 类型的值。

3. deterministic:

   • 表示函数是确定性的，即对于相同的输入参数，总是返回相同的输出结果。MySQL 可以对确定性函数进行优化。

4. begin ... end:

   • 函数体在 begin 和 end 之间定义，其中包含实际的实现逻辑。

本文由mdnice多平台发布