3.3.2.5. 转换为 Gaussdb-Mysql
3.3.2.5.1. 其他函数
3.3.2.5.1.1. CAST
- 语法
CAST (expr AS type)- 描述
- 将 expr 转换为指定的数据类型。
参数
参数 |
说明 |
|---|---|
expr |
要转换类型的值。 |
type |
转换的目标类型。 |
注意
- 数据类型转换仅支持相同类型或可以隐式转换的类型。此外，MySQL 与 GaussDB-Mysql 在数据类型处理上有一些差异。
例如，对于将字符串类型转换为数值类型， CAST(‘-123.45’ AS DECIMAL(10,2)) 在 MySQL 中的结果为 -123.45，而在 GaussDB-Mysql 中结果为 0.00。 此外，GaussDB-Mysql 会产生警告： WARNING: Truncated incorrect DECIMAL value: ‘-123.45’ 。
有关详细差异，请参阅文档中的相关章节：3.1.6 数据类型转换、3.2.5 强制转换函数等。 GaussDB-Mysql与MySQL兼容性差异
在 MySQL 中，NULL 和空字符串是不同的；而在 GaussDB-Mysql 中，这两者被视为相等，结果均为
NULL。
示例
-- 转换前MySQL SQL：
SELECT cast(1.5 as SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
| 2 |
+------------+
SELECT cast(1.5 as UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
| 2 |
+--------------+
SELECT cast('1.5' as SIGNED) as col_signed;
+------------+
| col_signed |
+------------+
| 1 |
+------------+
SELECT cast('1.5' as UNSIGNED) as col_unsigned;
+--------------+
| col_unsigned |
+--------------+
| 1 |
+--------------+
SELECT cast(1234 as DECIMAL) as col_decimal;
+-------------+
| col_decimal |
+-------------+
| 1234 |
+-------------+
SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATE) as col_date;
+------------+
| col_date |
+------------+
| 2024-01-31 |
+------------+
SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATETIME) as col_datetime;
+---------------------+
| col_datetime |
+---------------------+
| 2024-01-31 14:30:00 |
+---------------------+
-- 转换后Gaussdb-Mysql SQL：
SELECT cast(1.5 as SIGNED) as col_signed;
col_signed
------------
2
SELECT cast(1.5 as UNSIGNED) as col_unsigned;
col_unsigned
--------------
2
SELECT cast('1.5' as SIGNED) as col_signed;
WARNING: Truncated incorrect INTEGER value: '1.5'
CONTEXT: referenced column: col_signed
col_signed
------------
1
SELECT cast('1.5' as UNSIGNED) as col_unsigned;
WARNING: Truncated incorrect INTEGER value: '1.5'
CONTEXT: referenced column: col_unsigned
col_unsigned
--------------
1
SELECT cast(1234 as DECIMAL) as col_decimal;
col_decimal
-------------
1234
SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATE) as col_date;
col_date
------------
2024-01-31
SELECT cast(STR_TO_DATE('2024-01-31 14:30:00', '%Y-%m-%d %H:%i:%s') as DATETIME) as col_datetime;
col_datetime
---------------------
2024-01-31 14:30:00
3.3.2.5.1.2. CONVERT(expr,type)
- 语法
CONVERT(expr,type)- 描述
- 将 expr 转换为指定的数据类型 type。
方法参数和返回值
属性
描述
参数 1 (expr)
要转换的表达式，可以是列名、常量或函数调用。
参数 2 (type)
目标数据类型，范围包括：SIGNED, UNSIGNED, DECIMAL(M,D), CHAR, TIME, DATETIME。
返回值
将参数 1 (expr)转换为参数 2 (type) 指定的数据类型的值。
注意
- 数据类型转换仅支持相同类型或可以隐式转换的类型。此外，MySQL 与 GaussDB-Mysql 在数据类型处理上有一些差异。
例如，对于将字符串类型转换为数值类型， convert(‘-123.45’ , DECIMAL(10,2)) 在 MySQL 中的结果为 -123.45，而在 GaussDB-Mysql 中结果为 0.00。 此外，GaussDB-Mysql 会产生警告： WARNING: Truncated incorrect DECIMAL value: ‘-123.45’ 。
有关详细差异，请参阅文档中的相关章节：3.1.6 数据类型转换、3.2.5 强制转换函数等。 GaussDB-Mysql与MySQL兼容性差异
在 MySQL 中，NULL 和空字符串是不同的；而在 GaussDB-Mysql 中，这两者被视为相等，结果均为
NULL。目标数据类型 CHAR(N) 会限制转换结果的字符数，不超过指定的字符数 N。比如SELECT CONVERT(123.4567 , char(5)); 在源库maridb和目标库gaussdb mysql 505.2都转成123.4
目标数据类型 binary ，统一sql解析报错。
字符串转到时间time，如果字符串本身就不是时间格式，比如’Hello World’，在源库maridb转成空，在目标库gaussdb mysql 505.2版本转成00:00:00。
字符串转到日期时间datetime，如果字符串本身就不是时间格式，比如’Hello World’，在源库maridb和目标库gaussdb mysql 505.2都转成空。
字符串转到有符号整数(SIGNED)，SELECT CONVERT(‘abc’, SIGNED) AS invalid_signed ，在源库maridb和目标库gaussdb mysql 505.2都转成0。
无效的日期时间，比如SELECT CONVERT(‘2023-13-01’, DATETIME) AS invalid_datetime;SELECT CONVERT(‘25:61:61’, TIME) AS invalid_time;在源库maridb和目标库gaussdb mysql 505.2都转成NULL。
无符号整数和有符号数，超过边界就还是边界的值，无符号整数边界值18446744073709551615，有符合整数的边界值-9223372036854775808~9223372036854775807
示例
数据准备
========
创建测试表
--------
CREATE TABLE test_conversions (
id INT AUTO_INCREMENT PRIMARY KEY,
int_value INT,
decimal_value DECIMAL(10,2),
string_value VARCHAR(100),
time_value TIME,
datetime_value DATETIME,
unsigned_value INT UNSIGNED
);
插入测试数据
----------
INSERT INTO test_conversions (
int_value, decimal_value, string_value, time_value, datetime_value, unsigned_value
)
VALUES
(-123, 456.78, '2023-05-15', '14:30:00', '2023-05-15 14:30:00', 123),
(987, -65.43, 'Hello World', '23:59:59', '2022-12-31 23:59:59', 4294967295),
(0, 0.00, '123.45', '00:00:00', '2000-01-01 00:00:00', 0);
类型转换测试
=============
转换为 SIGNED（有符号整数）
------------------------
-- 转换前
SELECT string_value, CONVERT(string_value, SIGNED) AS to_signed,
CONVERT(decimal_value, SIGNED) AS decimal_to_signed
FROM test_conversions
ORDER BY id DESC;
-- 结果：
string_value|to_signed|decimal_to_signed|
------------+---------+-----------------+
123.45 | 123| 0|
Hello World | 0| -65|
2023-05-15 | 2023| 457|
-- 转换后
SELECT `string_value`, CONVERT(`string_value`, SIGNED) AS `to_signed`,
CONVERT(`decimal_value`, SIGNED) AS `decimal_to_signed`
FROM `test_conversions`
ORDER BY `id` DESC;
-- 转换后结果
string_value|to_signed|decimal_to_signed|
------------+---------+-----------------+
123.45 | 123| 0|
Hello World | 0| -65|
2023-05-15 | 2023| 457|
转换为 UNSIGNED（无符号整数）
--------------------------
-- 转换前
SELECT int_value, CONVERT(int_value, UNSIGNED) AS int_to_unsigned,
CONVERT(string_value, UNSIGNED) AS string_to_unsigned
FROM test_conversions
ORDER BY id DESC;
-- 结果：
int_value|int_to_unsigned |string_to_unsigned|
---------+--------------------+------------------+
0| 0| 123|
987| 987| 0|
-123|18446744073709551493| 2023|
-- 转换后
SELECT `int_value`, CONVERT(`int_value`, UNSIGNED) AS `int_to_unsigned`,
CONVERT(`string_value`, UNSIGNED) AS `string_to_unsigned`
FROM `test_conversions`
ORDER BY `id` DESC;
-- 转换后结果
int_value|int_to_unsigned |string_to_unsigned|
---------+--------------------+------------------+
0| 0| 123|
987| 987| 0|
-123|18446744073709551493| 2023|
转换为 DECIMAL
------------
-- 转换前
SELECT string_value, CONVERT(string_value, DECIMAL(5,2)) AS string_to_decimal,
CONVERT(int_value, DECIMAL(10,3)) AS int_to_decimal
FROM test_conversions;
-- 结果：
string_value|string_to_decimal|int_to_decimal|
------------+-----------------+--------------+
2023-05-15 | 999.99| -123.000|
Hello World | 0.00| 987.000|
123.45 | 123.45| 0.000|
-- 转换后
SELECT `string_value`, CONVERT(`string_value`, DECIMAL(5, 2)) AS `string_to_decimal`,
CONVERT(`int_value`, DECIMAL(10, 3)) AS `int_to_decimal`
FROM `test_conversions`;
-- 转换后结果
string_value|string_to_decimal|int_to_decimal|
------------+-----------------+--------------+
2023-05-15 | 999.99| -123.000|
Hello World | 0.00| 987.000|
123.45 | 123.45| 0.000|
转换为 CHAR
---------
-- 转换前
SELECT int_value, CONVERT(int_value, CHAR) AS int_to_char,
CONVERT(datetime_value, CHAR) AS datetime_to_char,
CONVERT(time_value, CHAR) AS time_to_char
FROM test_conversions;
-- 结果：
int_value|int_to_char|datetime_to_char |time_to_char|
---------+-----------+-------------------+------------+
-123|-123 |2023-05-15 14:30:00|14:30:00 |
987|987 |2022-12-31 23:59:59|23:59:59 |
0|0 |2000-01-01 00:00:00|00:00:00 |
-- 转换后
SELECT `int_value`, CONVERT(`int_value`, CHAR) AS `int_to_char`,
CONVERT(`datetime_value`, CHAR) AS `datetime_to_char`,
CONVERT(`time_value`, CHAR) AS `time_to_char`
FROM `test_conversions`;
-- 转换后结果：
int_value|int_to_char|datetime_to_char |time_to_char|
---------+-----------+-------------------+------------+
-123|-123 |2023-05-15 14:30:00|14:30:00 |
987|987 |2022-12-31 23:59:59|23:59:59 |
0|0 |2000-01-01 00:00:00|00:00:00 |
转换为 TIME
---------
-- 转换前
SELECT
string_value,
time_value,
CONVERT(string_value, TIME) AS string_to_time,
CONVERT(datetime_value, TIME) AS datetime_to_time
FROM test_conversions;
-- 结果：
string_value|time_value|string_to_time|datetime_to_time|
------------+----------+--------------+----------------+
2023-05-15 | 14:30:00| 00:20:23| 14:30:00|
Hello World | 23:59:59| | 23:59:59|
123.45 | 00:00:00| 00:01:23| 00:00:00|
-- 转换后
SELECT `string_value`, `time_value`,
CONVERT(`string_value`, TIME) AS `string_to_time`,
CONVERT(`datetime_value`, TIME) AS `datetime_to_time`
FROM `test_conversions`;
-- 转换后结果：
string_value | time_value | string_to_time | datetime_to_time
--------------+------------+----------------+------------------
2023-05-15 | 14:30:00 | 00:20:23 | 14:30:00
Hello World | 23:59:59 | 00:00:00 | 23:59:59
123.45 | 00:00:00 | 00:01:23 | 00:00:00
转换为 DATETIME
-------------
-- 转换前
SELECT
string_value,
datetime_value,
CONVERT(string_value, DATETIME) AS string_to_datetime,
CONVERT(time_value, DATETIME) AS time_to_datetime
FROM test_conversions;
-- 结果：
string_value|datetime_value |string_to_datetime |time_to_datetime |
------------+-------------------+-------------------+-------------------+
2023-05-15 |2023-05-15 14:30:00|2023-05-15 00:00:00|2025-05-26 14:30:00|
Hello World |2022-12-31 23:59:59| |2025-05-26 23:59:59|
123.45 |2000-01-01 00:00:00| |2025-05-26 00:00:00|
-- 转换后
SELECT `string_value`, `datetime_value`,
CONVERT(`string_value`, DATETIME) AS `string_to_datetime`,
CONVERT(`time_value`, DATETIME) AS `time_to_datetime`
FROM `test_conversions`;
-- 转换后结果：
string_value | datetime_value | string_to_datetime | time_to_datetime
--------------+---------------------+---------------------+---------------------
2023-05-15 | 2023-05-15 14:30:00 | 2023-05-15 00:00:00 | 2025-05-26 14:30:00
Hello World | 2022-12-31 23:59:59 | | 2025-05-26 23:59:59
123.45 | 2000-01-01 00:00:00 | | 2025-05-26 00:00:00
组合转换：从字符串到DATETIME再到DATE
--------------------------------
-- 转换前
SELECT
string_value,
CONVERT(
CONVERT(string_value, DATETIME),
DATE
) AS string_to_date
FROM test_conversions
WHERE CONVERT(string_value, DATETIME) IS NOT NULL;
-- 结果：
string_value|string_to_date|
------------+--------------+
2023-05-15 |2023-05-15 |
-- 转换后
SELECT `string_value`,
CONVERT(
CONVERT(`string_value`, DATETIME),
DATE
) AS `string_to_date`
FROM `test_conversions`
WHERE CONVERT(`string_value`, DATETIME) IS NOT NULL;
-- 转换后结果：
string_value|string_to_date|
------------+--------------+
2023-05-15 |2023-05-15 |
组合使用，字段显式类型转换（SELECT 子句）， 嵌套类型转换（表达式计算），WHERE条件过滤
===============================================================================================
-- 转换前
SELECT
id,
string_value,
CONVERT(string_value, DECIMAL(5,2)) AS price,
CONVERT(CONVERT(string_value, DECIMAL(5,2)) * 1.1, DECIMAL(5,2)) AS price_with_tax
FROM test_conversions
WHERE CONVERT(string_value, DECIMAL(5,2)) > 0;
-- 结果：
id|string_value|price |price_with_tax|
--+------------+------+--------------+
1|2023-05-15 |999.99| 999.99|
3|123.45 |123.45| 135.80|
-- 转换后
SELECT `id`,`string_value`,
CONVERT(`string_value`, DECIMAL(5, 2)) AS `price`,
CONVERT(CONVERT(`string_value`, DECIMAL(5, 2))*1.1, DECIMAL(5, 2)) AS `price_with_tax`
FROM `test_conversions`
WHERE CONVERT(`string_value`, DECIMAL(5, 2))>0
-- 转换后结果：
id|string_value|price |price_with_tax|
--+------------+------+--------------+
1|2023-05-15 |999.99| 999.99|
3|123.45 |123.45| 135.80|