函数

字符串函数

暂时未用到

数值函数

暂时未用到

日期函数

暂时未用到

if函数

if(value,t,f)：如果value为true则返回t，否则返回f。

mysql> select if(true,'yes','no');
+---------------------+
| if(true,'yes','no') |
+---------------------+
| yes                 |
+---------------------+
1 row in set (0.00 sec)

mysql> select if(false,'yes','no');
+----------------------+
| if(false,'yes','no') |
+----------------------+
| no                   |
+----------------------+
1 row in set (0.00 sec)

ifnull函数

ifnull(value1,value2)：如果value1不为空，则返回value1，否则返回value2

mysql> select ifnull('v1','v2');
+-------------------+
| ifnull('v1','v2') |
+-------------------+
| v1                |
+-------------------+
1 row in set (0.00 sec)

mysql> select ifnull(NULL,'v2');
+-------------------+
| ifnull(NULL,'v2') |
+-------------------+
| v2                |
+-------------------+
1 row in set (0.00 sec)

case when then else end函数

https://www.cnblogs.com/dirgo/p/9913708.html

mysql> CREATE DATABASE IF NOT EXISTS testdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)

mysql> use testdb;
Database changed

create table if not exists t_test_case(
  id bigint primary key auto_increment,
  flag varchar(64) not null
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 collate=utf8mb4_general_ci;

insert into t_test_case values (1,'1'),(2,'2'),(3,'3'),(4,'4');

mysql> update t_test_case set flag=(case id when 1 then 'v1' when 2 then 'v2' else 'v3' end) where id in(1,2,3);
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t_test_case;
+----+------+
| id | flag |
+----+------+
|  1 | v1   |
|  2 | v2   |
|  3 | v3   |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

mysql> update t_test_case set flag=(case when id=1 then 'v1' when id=2 then 'v2' else 'v3' end) where id in(1,2,3);
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from t_test_case;
+----+------+
| id | flag |
+----+------+
|  1 | v1   |
|  2 | v2   |
|  3 | v3   |
|  4 | 4    |
+----+------+
4 rows in set (0.00 sec)

uuid()函数

select uuid();

自定义函数

https://stackoverflow.com/questions/16737910/generating-a-random-unique-8-character-string-using-mysql

• 创建自定义函数

  DELIMITER $$
  
  DROP FUNCTION IF EXISTS `randStr`$$
  
  CREATE FUNCTION `randStr`(length SMALLINT(3)) RETURNS varchar(1024) CHARSET utf8 NO SQL
  begin
      SET @returnStr = '';
      SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
      SET @i = 0;
  
      WHILE (@i < length) DO
          SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
          SET @i = @i + 1;
      END WHILE;
  
      RETURN @returnStr;
  END$$
  DELIMITER ;

• 调用自定义函数

  select randStr(8);

创建自定义函数报告错误

Error Code: 1418. 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)报告错误时，需要在create function时指定NO SQL如下：

CREATE FUNCTION `randStr`(length SMALLINT(3)) RETURNS varchar(1024) CHARSET utf8 NO SQL
begin

返回指定范围的随机整数

https://stackoverflow.com/questions/984396/how-to-get-mysql-random-integer-range

返回0到4之间的随机整数（包含边界）

select FLOOR(0+RAND()*5)

返回1到5之间的随机整数（包含边界）

select FLOOR(1+RAND()*5)