在MySQL中如何进行数据类型的转换？

[yecao1227]

在MySQL中如何进行数据类型的转换？如：从float到int类型转换？在日期类型变量后加上一个int类型能否还得到日期类型？

[klan]

CONVERT() provides a way to convert data between different character sets. The syntax is:

CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.

Examples:

SELECT CONVERT(_latin1'Müller' USING utf8);
INSERT INTO utf8table (utf8column)
SELECT CONVERT(latin1field USING utf8) FROM latin1table;

CONVERT(... USING ...) is implemented according to the standard SQL specification.

You may also use CAST() to convert a string to a different character set. The syntax is:

CAST(character_string AS character_data_type CHARACTER SET charset_name)

Example:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

If you use CAST() without specifying CHARACTER SET, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables. If you use CAST() with CHARACTER SET X, the resulting character set and collation are X and the default collation of X.

You may not use a COLLATE clause inside a CAST(), but you may use it outside. That is, CAST(... COLLATE ...) is illegal, but CAST(...) COLLATE ... is legal.

Example:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

[hy2003fly]

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)

[eqxu]

顶


多谢


经典贴

[yecao1227]

谢谢大家的回复，另我又找到一个函数truncate(float,int)可以把float类型转换到int类型，具体语法自己看。