mysql数据类型转换

Question

mysql数据类型转换

Answer 1

123456789101112131415161718192021222324252627282930313233数字转字符mysql> SELECT CONCAT ( CAST(1 as char) , '2') AS test;+------+| test |+------+| 12   |+------+1 row in set (0.00 sec) mysql> SELECT CONCAT ( Convert(1, char) , '2') AS test;+------+| test |+------+| 12   |+------+1 row in set (0.00 sec) 字符转数字mysql> SELECT CAST('1' as SIGNED) + 100 AS test;+------+| test |+------+|  101 |+------+1 row in set (0.00 sec) mysql> SELECT Convert('1' , SIGNED) + 100 AS test;+------+| test |+------+|  101 |+------+1 row in set (0.00 sec)

Answer 2

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;