mysql常用函数

浏览数：92 / 时间：2015年06月12日

时间戳转换

datetime转换为时间戳：UNIX_TIMESTAMP()

时间戳转换为datetime:FROM_UNIXTIME()

mysql> create table testtime(id int auto_increment not null,timestr int,PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc testtime;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| timestr | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into testtime(timestr) values(UNIX_TIMESTAMP(‘2014-06-01 21:00:00‘));
Query OK, 1 row affected (0.00 sec)

mysql> select FROM_UNIXTIME(timestr) from testtime;
+------------------------+
| FROM_UNIXTIME(timestr) |
+------------------------+
| 2014-06-01 21:00:00    |
+------------------------+
1 row in set (0.00 sec)

mysql>

大小写转换

小写转换为大写：UPPER()

大写转换为小些：LOWER()

mysql> select LOWER(title) from film limit 4\G
*************************** 1. row ***************************
LOWER(title): academy dinosaur
*************************** 2. row ***************************
LOWER(title): ace goldfinger
*************************** 3. row ***************************
LOWER(title): adaptation holes
*************************** 4. row ***************************
LOWER(title): affair prejudice
4 rows in set (0.00 sec)