MySQL数据库

语法

函数名是大小写不敏感的，可以根据您的偏好书写：

SELECT database() -- 可以
SELECT DataBase() -- 可以
SELECT DATABASE() -- 可以

如果未设置 IGNORE_SPACE SQL_MODE，则函数名和第一个括号之间不能有空格，否则会返回错误代码 1064。默认情况下，IGNORE_SPACE 通常是禁用的（值为 0），这样解析器的处理速度更快。例如：

SELECT DATABASE () -- 通常不被接受
SELECT DATABASE() -- 始终有效

然而，这种限制仅适用于原生的 MySQL 函数。用户定义的函数（UDFs）和存储函数（stored functions）可以在名称后加空格。

您不能在 SELECT 子句中计算的值作为 WHERE 子句中的条件使用（这是一个“先有鸡还是先有蛋”的问题）。WHERE 子句是决定 SELECT 子句值的条件。要实现类似功能，可以使用 HAVING 子句，它在所有匹配行找到之后才应用。

---

通用函数

这些函数与数据类型无关。

BENCHMARK(times, expression)

• 执行指定表达式 n 次，并返回所用时间。
• 通常用于找出 SQL 表达式中的性能瓶颈。

SELECT BENCHMARK(10000, 'hello'); -- 处理时间约 0.0010 秒

CAST(value AS type)

• 将值转换为指定类型。

SELECT CAST(20130101 AS DATE); -- 返回 2013-01-01

CHARSET(string)

• 返回字符串使用的字符集。

SELECT CHARSET(20130101); -- 返回 binary
SHOW CHARACTER SET;       -- 显示所有已安装的字符集

COALESCE(value, ...)

• 返回第一个非 NULL 的参数。如果所有参数均为 NULL，则返回 NULL。至少需要一个参数。

SELECT COALESCE(NULL, 'hello', NULL); -- 返回 'hello'

COERCIBILITY(string)

• 返回字符串的强制性等级（值为 0 到 5）：

SELECT COERCIBILITY('hello'); -- 返回 4

强制性等级含义表：

强制性等级	含义	示例
0	显式排序规则	带有 COLLATE 子句的值
1	无排序规则	不同排序规则字符串的连接
2	隐式排序规则	列值
3	系统常量	USER() 的返回值
4	可强制性值	字符串字面量
5	可忽略	NULL 或派生自 NULL 的表达式

COLLATION(string)

• 返回字符串使用的排序规则。

SELECT COLLATION('hello'); -- 返回 utf8_general_ci

CONNECTION_ID()

• 返回当前线程的 ID。

SELECT CONNECTION_ID(); -- 返回 31

CONVERT(value, type)

• 将值转换为指定类型。

SELECT CONVERT('666', UNSIGNED INTEGER);

CONVERT(string USING charset)

• 将字符串转换为指定字符集。

SELECT CONVERT('This is a text' USING utf8);

CURRENT_USER()

• 返回当前连接使用的用户名和主机名。

SELECT CURRENT_USER();
SELECT CURRENT_USER; -- 同样有效

DATABASE()

• 返回当前使用的数据库名称（由 USE 命令设置）。

SELECT DATABASE();

FOUND_ROWS()

在使用带有 LIMIT 子句和 SQL_CALC_FOUND_ROWS 关键字的 SELECT 查询后，可以通过运行另一个查询使用 FOUND_ROWS() 函数。此函数返回上一条查询在没有 LIMIT 子句时的结果总行数。

SELECT SQL_CALC_FOUND_ROWS * FROM stats ORDER BY id LIMIT 10 OFFSET 50;
SELECT FOUND_ROWS() AS n;

---

GREATEST(value1, value2, ...)

返回传入参数中最大的值。

---

IF(val1, val2, val3)

• 如果 val1 为 TRUE，返回 val2。
• 如果 val1 为 FALSE 或 NULL，返回 val3。

---

IFNULL(val1, val2)

• 如果 val1 为 NULL，返回 val2；
• 否则，返回 val1。

---

ISNULL(value)

• 如果传入的值为 NULL，返回 1；
• 否则返回 0。

---

INTERVAL(val1, val2, val3, ...)

返回第一个参数大于列表中参数时的位置，从 0 开始计数：

SELECT INTERVAL(10, 20, 9, 8, 7);  -- 返回 0
SELECT INTERVAL(10, 9, 20, 8, 7);  -- 返回 1
SELECT INTERVAL(10, 9, 8, 20, 7);  -- 返回 2
SELECT INTERVAL(10, 9, 8, 7, 20);  -- 返回 3

---

NULLIF(val1, val2)

• 如果 val1 = val2，返回 NULL；
• 否则返回 val1。

---

LAST_INSERT_ID()

返回数据库中最近插入的 AUTO_INCREMENT 的 ID。常用于两条记录之间需要主外键关联的情况，避免额外的 SELECT 查询。

---

LEAST(value1, value2, ...)

返回传入参数中最小的值。

---

SUBSTR(string, start, size)

截取字符串。

SELECT SUBSTR('Hello World!', 7, 5); -- 返回 'World'

---

日期和时间函数

MySQL 提供了丰富的日期相关函数，以下是一些示例：

按时间范围查询：

SELECT * FROM mytable
WHERE datetimecol >= (CURDATE() - INTERVAL 1 YEAR)  
  AND datetimecol < (CURDATE() - INTERVAL 1 YEAR) + INTERVAL 1 DAY;

获取下一个 15 日的日期：

SELECT IF(DAYOFMONTH(CURDATE()) <= 15,
  DATE_FORMAT(CURDATE(), '%Y-%m-15'),
  DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-15')) AS next15
FROM table;

分解日期：

SELECT YEAR('2002-05-10'), MONTH('2002-05-10'), DAYOFMONTH('2002-05-10');

查询特定年份的记录：

SELECT PurchaseDate FROM table WHERE YEAR(PurchaseDate) <= YEAR(CURDATE());

按时间段查询：

SELECT columns FROM table
WHERE start_time >= '2004-06-01 10:00:00' 
  AND end_time <= '2004-06-03 18:00:00';

格式化时间范围：

SELECT * FROM t1
WHERE DATE_FORMAT(datetime_column, '%T') BETWEEN 'HH:MM:SS' AND 'HH:MM:SS';

计算最近的时间范围：

SELECT Start_time, End_time FROM Table
WHERE Start_time >= NOW() - INTERVAL 4 HOUR;

时间偏移：

SELECT NOW() + INTERVAL 60 SECOND;

时间戳与日期互相转换：

SELECT UNIX_TIMESTAMP('2007-05-01'); -- 返回 1177970400
SELECT FROM_UNIXTIME(1177970400);    -- 返回 '2007-05-01 00:00:00'

注意：

• 日期转换格式需符合 ISO 标准，否则会返回 NULL。
• 错误示例：

  SELECT CONVERT('17/02/2016 15:49:03', DATETIME); -- 返回 NULL
  

• 正确示例：

  SELECT CONVERT('2016-02-17 15:49:03', DATETIME); -- 返回有效日期
  SELECT CONVERT('2016/02/17 15:49:03', DATETIME);
  

聚合函数

COUNT(field)

• 如果使用 *，COUNT() 返回查询找到的行数，常用于获取表中的总行数。

  SELECT COUNT(*) FROM `antiques`;
  

• 如果使用 DISTINCT 关键字，相同的行只会被计算一次。

  SELECT COUNT(DISTINCT *) FROM `antiques`;
  

• 如果指定字段名，COUNT() 返回该字段中非 NULL 的值的数量。

  SELECT COUNT(`cost`) FROM `antiques`;
  

• 如果同时使用字段名和 DISTINCT 关键字，返回非 NULL 值的数量，并且相同的值只会被计算一次。

  SELECT COUNT(DISTINCT `cost`) FROM `antiques`;
  

• 也可以对表达式中的非 NULL 值计数：

  SELECT COUNT(`longitude` + `latitude`) FROM `cities`;
  

  此查询返回 longitude 和 latitude 均为非 NULL 的行数。

---

MAX(field)

• 返回查询匹配的行中某字段的最大值。如果没有匹配行，则返回 NULL。

  SELECT MAX(`cost`) FROM `antiques`;
  SELECT MAX(LENGTH(CONCAT(`first_name`, ' ', `last_name`))) FROM `subscribers`;
  

---

MIN(field)

• 返回查询匹配的行中某字段的最小值。如果没有匹配行，则返回 NULL。

  SELECT MIN(`cost`) FROM `antiques`;
  

---

AVG(field)

• 返回查询匹配的行中某字段的平均值。如果没有匹配行，则返回 NULL。

  SELECT AVG(`cost`) FROM `antiques`;
  

---

SUM(field)

• 返回查询匹配的行中某字段的总和。如果没有匹配行，则返回 NULL。

  • 使用 SUM(DISTINCT expression) 时，相同的值只会被计算一次。此功能在 MySQL 5.1 中引入。

  SELECT SUM(`cost`) FROM `antiques`;
  

---

GROUP_CONCAT(field)

• 将组中所有记录的值连接为一个字符串，默认以逗号分隔。可以通过指定分隔符自定义分隔方式。

  创建示例表：

  CREATE TEMPORARY TABLE product (
      id INTEGER, product_type VARCHAR(10), product_name VARCHAR(50)
  );
  
  INSERT INTO product VALUES
      (1, 'mp3', 'iPod'),
      (2, 'mp3', 'Zune'),
      (3, 'mp3', 'ZEN'),
      (4, 'notebook', 'Acer Eee PC'),
      (4, 'notebook', 'Everex CloudBook');
  

  示例查询：

  SELECT product_type, GROUP_CONCAT(product_name)
  FROM product
  GROUP BY product_type;
  
  SELECT product_type, GROUP_CONCAT(' ', product_name)
  FROM product
  GROUP BY product_type;
  

---

聚合位运算函数

通用语法：

FUNCTION_NAME('expression')

这些函数对结果集中每一行的表达式进行计算，并对所有表达式进行聚合计算。以下为 64 位精度的位运算函数：

• AND:

  SELECT BIT_AND(ip) FROM log;
  

• OR:

  SELECT BIT_OR(ip) FROM log;
  

  如果没有行，返回 0。

• XOR:

  SELECT BIT_XOR(ip) FROM log;
  

  如果没有行，返回 0。

---

参考文档

• MySQL 信息函数
• MySQL 日期与时间函数