MySQL数据库内置函数

---

一、日期函数

• 获取时间演示

-- 获得时间
mysql> select current_time;
+--------------+
| current_time |
+--------------+
| 20:39:10     |
+--------------+
1 row in set (0.00 sec)
-- now()获得日期加时间
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2022-09-27 20:47:17 |
+---------------------+
1 row in set (0.00 sec)


-- 获取时间戳
mysql> select current_timestamp;
+---------------------+
| current_timestamp   |
+---------------------+
| 2022-09-27 20:38:26 |
+---------------------+
1 row in set (0.00 sec)
-- 获取一个时间类型的日期部分
mysql> select date('2022-9-27 20:40');
+-------------------------+
| date('2022-9-27 20:40') |
+-------------------------+
| 2022-09-27              |
+-------------------------+
1 row in set (0.00 sec)

• 日期加减演示

mysql> select date_add('2022-9-27 20:40', interval 3 day);
+---------------------------------------------+
| date_add('2022-9-27 20:40', interval 3 day) |
+---------------------------------------------+
| 2022-09-30 20:40:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add('2022-9-27 20:40', interval 3 month);
+-----------------------------------------------+
| date_add('2022-9-27 20:40', interval 3 month) |
+-----------------------------------------------+
| 2022-12-27 20:40:00                           |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select date_sub('2022-9-27 20:40', interval 3 day);
+---------------------------------------------+
| date_sub('2022-9-27 20:40', interval 3 day) |
+---------------------------------------------+
| 2022-09-24 20:40:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

-- 前一个日期减去后一个日期
mysql> select datediff('2022-9-27', '2022-10-1' );
+-------------------------------------+
| datediff('2022-9-27', '2022-10-1' ) |
+-------------------------------------+
|                                  -4 |
+-------------------------------------+
1 row in set (0.00 sec)

二、字符串函数

• 注意：length函数返回字符串长度，以字节为单位。
• 如果是多字节字符则计算多个字节数；
• 如果是单字节字符则算作一个字节。
• 比如：英文字母、符合等用一个字节就可以表示，中文需要多个字节表示（与字符集编码有关）

-- 这里使用是utf-8
mysql> select length('1wqeerr');
+-------------------+
| length('1wqeerr') |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.00 sec)

mysql> select length('我是中文输入法');
+---------------------------------+
| length('我是中文输入法')        |
+---------------------------------+
|                              21 |
+---------------------------------+
1 row in set (0.00 sec)

三、数学函数

mysql> select abs(-99.99);
+-------------+
| abs(-99.99) |
+-------------+
|       99.99 |
+-------------+
1 row in set (0.00 sec)

mysql> select format(9.123457, 5);
+---------------------+
| format(9.123457, 5) |
+---------------------+
| 9.12346             |
+---------------------+
1 row in set (0.00 sec)

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.5247921184867547 |
+--------------------+
1 row in set (0.00 sec)

mysql> select floor(rand()*100);
+-------------------+
| floor(rand()*100) |
+-------------------+
|                65 |
+-------------------+
1 row in set (0.00 sec)

mysql> select floor(rand()*100); // 获得0 ~ 99的数
+-------------------+
| floor(rand()*100) |
+-------------------+
|                68 |
+-------------------+
1 row in set (0.00 sec)

四、其它函数

• user() 查询当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

• md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

mysql> select md5('asdfasdf');
+----------------------------------+
| md5('asdfasdf')                  |
+----------------------------------+
| 6a204bd89f3c8348afd5c77c717a097a |
+----------------------------------+
1 row in set (0.00 sec)

• database()显示当前正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> use test9_18;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select database();
+------------+
| database() |
+------------+
| test9_18   |
+------------+
1 row in set (0.00 sec)

• password()函数，MySQL数据库使用该函数对用户加密

select password('root');
 +-------------------------------------------+
| password('root')             |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+

• ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值
  有点类似三目运算符 ? :

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

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