MySQL内置函数

24. last_insert_id()

函数：

 last_insert_id()：

返回一个64-bit的值，表示作为最近一条insert语句的执行结果，被成功插入到一个auto_increment列的第一个自动生成的值。如果没有行被成功插入，则last_insert_id()的值不变。从MySQL 5.5.29开始，该函数的返回类型为bigint unsigned，之前版本的类型为bigint；

 last_insert_id(expr)：

返回expr，并设置该连接下次调用last_insert_id()的返回值为expr。从MySQL 5.5.29开始，该函数的返回类型为int unsigned，之前版本的类型为int；

示例：

例如在插入了一个能够生成auto_increment值的行之后，你可以按如下方式获取该值：

24.1 last_insert_id()

 如果一个insert语句同时插入了多行，则last_insert_id()返回的是第一个被插入行的auto_increment值。这样做的目的是为了能够在其它服务器上轻松地复制相同的insert语句，如下所示：

虽然insert语句向表中插入了三行，但第一个被插入行的id值为2，这就是select语句中last_insert_id()的返回值

 当前正在执行的语句不会影响last_insert_id()的值

假设你使用一个语句生成了一个auto_increment的值，然后在另一个多行插入语句中引用了last_insert_id()，此多行插入语句向其它具有auto_increment列的表插入了多行，则last_insert_id()的值在第二个语句（即多行插入语句）中将保持稳定，其插入的第二行及之后的所有行都不受之前插入行的影响，（但是如果混合引用last_insert_id()和last_insert_id(expr)，则结果未定义），如下图所示：

 生成的id值以每条连接为基础维护在server端

这意味着，该函数返回给客户端的是该客户端最近一条影响auto_increment列的语句中生成的第一个auto_increment值，与其它客户端或连接以及会话无关。该值不受其它客户端影响，即使其它客户端生成了自己的auto_increment值

这种方式能够确保每个客户端无需关心其它客户端的活动，自由检索自己的id值，并且不需要锁或事务的帮助

 若将行的auto_increment列设置为一个非"magic"的值（既不为NULL，也不为0的值），last_insert_id()的值不改变

 如果上一条语句出错，则last_insert_id()的值未定义；对于事务表，如果语句由于错误而回滚，则last_insert_id()的值未定义；对于手动rollback，last_insert_id()的值不会恢复到事务之前的值，它仍旧位于rollback时的点

 insert ignore 

如果你使用的是insert ignore并且该行被忽略，则last_insert_id()保持不变（如果该连接从未成功地执行过insert，则返回0）

同时，对于非事务表，auto_increment计数器不递增；对于InnoDB表，如果innodb_autoinc_lock_mode被设置为1或2，则auto_increment计数器将递增，如下所示：

 stored routine和trigger

在stored routine（procedure / function）或trigger的定义体中，last_insert_id()的值的改变方式与在这些对象外部执行的语句中的改变方式相同。stored routine和trigger对后面语句所看到的last_insert_id()的值的影响取决于例程的类型：

a. 如果stored procedure中的语句改变了last_insert_id()的值，则此更改对stored procedure之后的语句可见；

b. 如果stored function和trigger改变了last_insert_id()的值，当函数或触发器结果时，该值将被恢复，因此后面的语句将不会看到更改的值；

 在MySQL 5.5.35之前，如果使用复制过滤规则，则这个函数不会被正确复制（bug #17234370， bug #69861）

24.2 last_insert_id(expr)

如果将expr作为参数传递给last_insert_id()，则expr将是last_insert_id(expr)的返回值，同时也是下次调用last_insert_id()的返回值，即：

 last_insert_id(expr) = expr

 下次last_insert_id() = expr

这可以用来生成序列：

① 首先创建一个表来保存序列计数器并初始化：

② 以如下方式使用该表来生成序列号：

update语句将递增序列计数器，并使得下一个last_insert_id()调用返回更新后的值。select语句则负责检索该值。你还可以使用C API函数mysql_insert_id()来获取该值

当然，last_insert_id()不是生成序列号的唯一方式，但是使用last_insert_id()的好处是，将id值作为前一次自动生成的值维护在server端，同时它是多用户安全的，多个客户端可以同时发起update操作，然后各自使用select语句（或mysql_insert_id()函数）来获取属于自己的序列号。这样既不影响其它客户端，也不会被其它客户端所影响

请注意，mysql_insert_id()只在insert和update语句之后更新，因此在执行了其它类型的SQL语句（如select或set）之后，你不能使用该C API函数来检索last_insert_id(expr)的值

23. database()

函数：database()

功能：返回当前数据库的名字

示例：

22. current_user()

函数：current_user()

功能：返回当前连接的账号名

返回值：'user_name'@'host_name'

等价：CURRENT_USER

示例：

21. to_days()

函数：to_days(date)

功能：返回日期对应的天数，计算从0000-00-00开始经过的天数

参数：date类型的日期值，MySQL会自动将两位数的年份转换成四位数的年份，即“95-08-25”和“950825”等价于“1995-08-25”

说明：date不可为1582年之前的日期

示例：

20. curdate()

函数：curdate()

功能：将当前日期以"YY-MM-DD"或YYMMDD格式返回，取决于该函数所处的上下文（字符串或数值上下文）

等价：current_date()，current_date

示例：

19. curtime()

函数：curtime()

功能：将当前时间以"HH:MM:SS"或HHMMSS格式返回，取决于该函数所处的上下文（字符串或数值上下文）

等价：current_date()，current_date

示例：

18. floor()

格式：floor(X)

功能：返回不大于X的最大整数

示例：

17. rand函数

格式：rand(N)

功能：返回一个随机浮点数，范围 [0, 1)

参数：N为随机数种子，可省略

◇ 若N为常量，则在执行前只初始化一次随机数种子

◇ 若N为非常量，例如字段名，则每次调用rand()时都用相应的值初始化一次种子

说明：

① 若N相同，则生成的随机数都一样

② 要获取 [i, j)之间的一个随机整数，可以使用floor(i + rand() * (j-i))

③ 当rand()用在where子句时，每行计算一次

示例：

获取 [7, 12)的随机整数

16. right函数

格式：right(str, len)

功能：返回字符串str右侧前len个字符

示例：

15. left函数

格式：left(str, len)

功能：返回字符串str左侧前len个字符

示例：

14. convert函数

格式：convert(str USING encode)

功能：将字符串「str」转换为「encode」编码

参数：「encode」为MySQL对应字符集的名字

示例：select convert("abc" USING utf8);

格式：convert(expr, type)

功能：将expr转换为type类型的结果

示例：select convert(1/3, decimal(4, 3));

13. cast函数

等同于convert(expr, type)

12. date_add函数

格式：date_add(time, INTERVAL expr unit)

功能：执行日期计算

参数：

time 起始时间，可为date或datetime类型

expr 间隔时间，将被加到time

unit 指定expr的单位

返回值：

① 以下情况，返回NULL

    ◇ 「time」为非法日期，如「2006-07-00」

    ◇ 「time」格式非法，如「201704-30」

② 以下情况，返回datetime

    ◇ 「time」为datetime

    ◇ 「time」为date，「unit」使用了「HOURS」「MINUTES」或「SECONDS」

③ 其它情况下，返回date

注意事项：

① 若expr对于unit来说太短，则高位补0

即 INTERVAL "01:45" MINUTE_MICROSECOND 等价于「00:01.000045」 0分1秒45微秒

② expr被视作字符串，因此为expr指定非字符串值时，需要小心

即 INTERVAL 6/4 HOUR_MINUTE 等价于 INTERVAL 1.5000 HOUR_MINUTE，即1小时5000分钟

解决办法：使用CAST进行转换，INTERVAL CAST(6/4 AS DECIMAL(3, 1)) HOUR_MINUTE 

说明：

月份自动转换，即date_add("2017-04-29", INTERVAL 2 DAY)，结果为"2017-05-01"

等价

等同于「time」+ INTERVAL expr unit

INTERVAL expr unit 可以和「time」互换位置，即：INTERVAL expr unit +「time」

unit取值和expr格式，对应关系

11. date_sub函数

参考date_add

等价

等同于「time」- INTERVAL expr unit

INTERVAL expr unit不能放在减号左侧，因为从时间区间减值，没有意义

10. unhex函数

格式：unhex(str)

功能：将十六进制字符串还原为原始数据

说明：对于字符串参数str，unhex(str)将参数中的每对字符当做两个十六进制数，并将其转换为由该数表示的字节。返回值是二进制字符串

返回值：成功返回二进制字符串；若参数中有非法字符，则返回NULL

示例：

9. hex函数

格式：hex(binary_data)

功能：使用十六进制字符串来表示二进制数据

说明：hex将binary_data中的每个字节，都转化成对应的两位十六进制值的字符串形式

参数：binary_data可为任意类型的数据，如字符串和数字

返回值：返回十六进制字符串

示例：

因为字符"a"对应的十六进制值为0x61，"b"=0x62，"c"=63，故字符串"abc"的十六进制字符串为"616263"

因为整数255的十六进制值为0xff，故整数255的十六进制字符串为"FF"

因为二进制数b'01101101'的十六进制值为0x6d，故二进制数b'01101101'的十六进制字符串为"6D"

因为十六进制数x'2bfe'的十六进制值为0x2BFE，故十六进制数x'2bfe'的十六进制字符串为"2BFE"

8. conv函数

格式：conv(N, from_base, to_base)

功能：数字的进制转换

参数：

N 原始数字，可为整数或内容为整数的字符串

from_base 原进制

to_base 目标进制

返回值：成功返回转化后结果的字符串表示，失败返回NULL

示例：

7. length函数

格式：length(arg)

功能：返回字符串占用的字节数

参数：参数可为字符串字面值，或字符串类型的字段名

6. char_length函数

格式：char_length(arg)

功能：返回字符串中的字符个数

参数：参数可为字符串字面值，或字符串类型的字段名

5. bit_length函数

格式：bit_length(arg)

功能：返回字符串占用的位数

参数：参数可为字符串字面值，或字符串类型的字段名

4. max函数

格式：max(FieldName)

功能：获取字段的最大值

示例：

3. min函数

格式：min(FieldName)

功能：获取字段的最小值

示例：

2. replace函数

格式：replace(str, from, to)

功能：将字符串str中的所有子串from替换为子串to

例如：

获取tb_user_device表中的guid, chns字段，同时将chns字段中的子串"nm":""删除

select guid, replace(chns, concat('"nm":""'), '') from tb_user_device;

1. concat函数

格式：concat(str1, str2...)

功能：返回由各参数首尾连接而成的字符串，参数可为字段名

例如：

查询chns字段包含「"nm":"xxx」的记录数，「xxx」为同一记录中guid的字段值

select count(*) from tb_user_device where chns like concat('%"nm":"', guid, '%');