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, '%');  


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值