MySql中的数据类型转换注意事项

来源：互联网发布：软件测试学费多少编辑：程序博客网时间：2024/06/08 06:28

注意可转换成的数据类型只能是：binary，char，date，datetime，decimal，signed，unsigned，time
并没有常用的 int, varchar

MySQL的类型转换函数如下：
CAST(expr AS type), CONVERT(expr,type), CONVERT(expr USING transcoding_name)

The CAST() and CONVERT() functions take a value of one type and produce a value of another type.

The type can be one of the following values:

BINARY[(N)]

CHAR[(N)]

DATE

DATETIME

DECIMAL

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

mysql> select cast(now() as char);
+---------------------+
| cast(now() as char) |
+---------------------+
| 2006-06-15 09:31:20 |
+---------------------+

mysql> select convert( '中文 ' using gb2312);
+------------------------------+
| convert( '中文 ' using gb2312) |
+------------------------------+
| 中文 |
+------------------------------+
1 row in set (0.06 sec)

使用日期函数date_add,date_sub就可以了,见下例：
mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2006-06-16 09:36:06 |
+--------------------------------+
1 row in set (0.02 sec)

mysql> select date_add(now(), interval 1 month);
+-----------------------------------+
| date_add(now(), interval 1 month) |
+-----------------------------------+
| 2006-07-15 09:36:38 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(), interval 1 minute);
+------------------------------------+
| date_add(now(), interval 1 minute) |
+------------------------------------+
| 2006-06-15 09:38:03 |
+------------------------------------+
1 row in set (0.00 sec)

1 row in set (0.05 sec)

0 0