电脑可以做服务器部署网站吗,分类信息网站开发教程,黑科技软件网站,个人网站搭建详细步骤在 MySQL 中#xff0c;CAST() 和 CONVERT() 函数都用于数据类型转换。虽然这两个函数在大多数情况下可以互换使用#xff0c;但它们之间还是有一些细微的差别。
官方文档地址
https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html#function_cast
CAST() 函数
C…在 MySQL 中CAST() 和 CONVERT() 函数都用于数据类型转换。虽然这两个函数在大多数情况下可以互换使用但它们之间还是有一些细微的差别。
官方文档地址
https://dev.mysql.com/doc/refman/8.4/en/cast-functions.html#function_cast
CAST() 函数
CAST() 函数是 SQL 标准中的数据类型转换函数。其基本语法如下
CAST(expression AS type)CAST(timestamp_value AT TIME ZONE timezone_specifier AS DATETIME[(precision)])timezone_specifier: [INTERVAL] 00:00 | UTCexpression 是要转换的值或表达式。type 是要转换为的数据类型。
例如将整数转换为字符串
(rootlocalhost:mysql.sock)[(superdb)]SELECT CAST(8860 AS CHAR) as v1;
------
| v1 |
------
| 8860 |
------
1 row in set (0.00 sec)强制转换函数对于在CREATE TABLE中创建具有特定类型的列很有用。。。SELECT语句 The cast functions are useful for creating a column with a specific type in a CREATE TABLE … SELECT statement
(rootlocalhost:mysql.sock)[superdb]CREATE TABLE t_new_table SELECT CAST(2024001 AS decimal(18,0)) as id,CAST(2000-01-01 AS DATE) AS col_1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0(rootlocalhost:mysql.sock)[superdb]show create table t_new_table;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t_new_table | CREATE TABLE t_new_table (id decimal(18,0) NOT NULL DEFAULT 0,col_1 date DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)(rootlocalhost:mysql.sock)[superdb]desc t_new_table;
-------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
-------------------------------------------------
| id | decimal(18,0) | NO | | 0 | |
| col_1 | date | YES | | NULL | |
-------------------------------------------------
2 rows in set (0.00 sec)(rootlocalhost:mysql.sock)[superdb]select * from t_new_table;
---------------------
| id | col_1 |
---------------------
| 2024001 | 2000-01-01 |
---------------------
1 row in set (0.00 sec)
强制转换为有符号或无符号的64位整数
use the SIGNED or UNSIGNED cast operator to cast a value to a signed or unsigned 64-bit integer
(rootlocalhost:mysql.sock)[superdb]SELECT 8-9 as v1,CAST(8 - 9 AS SIGNED) as v2, CAST(8 - 9 AS UNSIGNED) as v3;
------------------------------
| v1 | v2 | v3 |
------------------------------
| -1 | -1 | 18446744073709551615 |
------------------------------
1 row in set (0.00 sec)
从MySQL 8.0.22开始CAST支持使用AT TIMEZONE运算符检索以UTC为单位的TIMESTAMP值。唯一支持的时区是UTC这可以指定为“000:00”或“UTC”。此语法支持的唯一返回类型是DATETIME其可选精度说明符范围为0到6包括0到6
Beginning with MySQL 8.0.22, CAST() supports retrieval of a TIMESTAMP value as being in UTC, using the AT TIMEZONE operator. The only supported time zone is UTC; this can be specified as either of 00:00 or UTC. The only return type supported by this syntax is DATETIME, with an optional precision specifier in the range of 0 to 6, inclusive.
TIMESTAMP values that use timezone offsets are also supported. (rootlocalhost:mysql.sock)[superdb] SELECT system_time_zone;
--------------------
| system_time_zone |
--------------------
| CST |
--------------------
1 row in set (0.00 sec)(rootlocalhost:mysql.sock)[superdb] CREATE TABLE t_cast_timezone (col_convert_datetime TIMESTAMP);
Query OK, 0 rows affected (0.06 sec)(rootlocalhost:mysql.sock)[superdb] INSERT INTO t_cast_timezone VALUES ROW(CURRENT_TIMESTAMP),ROW(2024-06-15 14:50:15);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0(root192.168.80.85)[superdb] TABLE t_cast_timezone;
----------------------
| col_convert_datetime |
----------------------
| 2024-06-15 22:06:07 |
| 2024-06-15 14:50:15 |
----------------------
2 rows in set (0.00 sec)(rootlocalhost:mysql.sock)[superdb] SELECT CAST(col_convert_datetime AT TIME ZONE 00:00 AS DATETIME) AS u FROM t_cast_timezone;
---------------------
| u |
---------------------
| 2024-06-15 14:06:07 |
| 2024-06-15 06:50:15 |
---------------------
2 rows in set (0.00 sec)
CONVERT() 函数
CONVERT() 函数在某些数据库系统如 MySQL中提供但在其他系统中可能不可用。其基本语法如下
CONVERT(expression, type)或在某些数据库系统中
CONVERT(type, expression)但请注意在 MySQL 中CONVERT() 函数的语法与 CAST() 类似
CONVERT(expression, type)expression 是要转换的值或表达式。type 是要转换为的数据类型。
例如在 MySQL 中将整数转换为字符串与 CAST() 函数的示例相同
(rootlocalhost:mysql.sock)[superdb]SELECT CONVERT(123890,CHAR) as v1;
--------
| v1 |
--------
| 123890 |
--------
1 row in set (0.01 sec)
例如在 MySQL 中将整数转换为双精度decimal类型的示例
decimal 类型可以存储大量的数字并且具有可配置的精度。例如decimal(18,2) 可以存储最多 18 位数字其中 2 位在小数点之后
(rootlocalhost:mysql.sock)[superdb]SELECT CONVERT(123890,decimal(18,2)) as v1;
-----------
| v1 |
-----------
| 123890.00 |
-----------
1 row in set (0.00 sec)-- 将字符串转换为双精度decimal类型的示例(rootlocalhost:mysql.sock)[superdb]SELECT CONVERT(123890,decimal(18,2)) as v1,CONVERT(123890,decimal(18,2)) as v2;
----------------------
| v1 | v2 |
----------------------
| 123890.00 | 123890.00 |
----------------------
1 row in set (0.00 sec)
将日期字符串转换为日期类型
(rootlocalhost:mysql.sock)[superdb]SELECT CONVERT(2024-06-13, DATE) as v1,CONVERT(2024-06-13 13:16:24, DATETIME) as v2;
---------------------------------
| v1 | v2 |
---------------------------------
| 2024-06-13 | 2024-06-13 13:16:24 |
---------------------------------
1 row in set (0.00 sec)
字符集转换在二进制字符串的字母大小写转换之前也很有用。LOWER和UPPER在直接应用于二进制字符串时是无效的因为lettercase的概念不适用。
Character set conversion is also useful preceding lettercase conversion of binary strings. LOWER() and UPPER() are ineffective when applied directly to binary strings because the concept of lettercase does not apply. To perform lettercase conversion of a binary string, first convert it to a nonbinary string using a character set appropriate for the data stored in the string
(rootlocalhost:mysql.sock)[superdb]SET str BINARY New York;
Query OK, 0 rows affected, 1 warning (0.00 sec)(rootlocalhost:mysql.sock)[superdb]SELECT LOWER(str), LOWER(CONVERT(str USING utf8mb4));
--------------------------------------------------------------
| LOWER(str) | LOWER(CONVERT(str USING utf8mb4)) |
--------------------------------------------------------------
| 0x4E657720596F726B | new york |
--------------------------------------------------------------
1 row in set (0.00 sec)
使用 CONVERT() 进行字符集转换注意在某些数据库系统中CONVERT() 可能不支持字符集转换但MySQL的 CONVERT() 函数在 USING 子句的支持下可以这样做
两者之间的差别
SQL 标准CAST() 是 SQL 标准中的函数而 CONVERT() 并非所有数据库系统都支持。语法虽然在 MySQL 中 CONVERT() 的语法与 CAST() 类似但在其他数据库中可能有所不同。功能在某些数据库中CONVERT() 可能提供额外的功能或选项这些功能在 CAST() 中不可用。但在 MySQL 中这两个函数在功能上非常相似。可读性有时CONVERT() 可能会被认为更具可读性因为它更接近于许多编程语言中的类型转换语法。但是由于 CAST() 是 SQL 标准中的函数因此它通常更受推荐。
总结
在 MySQL 中CAST() 和 CONVERT() 都可以用于数据类型转换并且它们在功能上非常相似。然而由于 CAST() 是 SQL 标准中的函数因此通常更推荐使用它。但在其他数据库系统中您可能需要检查这两个函数的可用性和功能差异。
