MariaDB CAST语法
2024-09-27 09:49:46
Syntax
CAST(expr AS type)
Description
CAST（）函数采用一种类型的值，并产生另一种类型的值，类似于CONVERT函数。
CAST（）和CONVERT（）之间的主要区别是CONVERT（expr，type）是ODBC语法，而CAST（expr as type）和CONVERT（...USING ...)` 是SQL92语法。
Examples
简单的转换：
SELECT CAST("abc" AS BINARY);
SELECT CAST("1" AS UNSIGNED INTEGER);
SELECT CAST(123 AS CHAR CHARACTER SET utf8)
注意，当不指定字符集而将字符强制转换为CHAR时，将使用collation_connection字符集排序规则。与CHAR CHARACTER SET一起使用时，将使用该字符集的默认排序规则。
SELECT COLLATION(CAST(123 AS CHAR));
+------------------------------+
| COLLATION(CAST(123 AS CHAR)) |
+------------------------------+
| latin1_swedish_ci |
+------------------------------+
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8));
+-------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)) |
+-------------------------------------------------+
| utf8_general_ci |
+-------------------------------------------------+
如果您还想更改排序规则，则必须使用COLLATE运算符：
SELECT COLLATION(CAST(123 AS CHAR CHARACTER SET utf8)
COLLATE utf8_unicode_ci);
+-------------------------------------------------------------------------+
| COLLATION(CAST(123 AS CHAR CHARACTER SET utf8) COLLATE utf8_unicode_ci) |
+-------------------------------------------------------------------------+
| utf8_unicode_ci |
+-------------------------------------------------------------------------+
使用CAST（）将ENUM字段排序为CHAR而不是内部数值：
CREATE TABLE enum_list (enum_field enum('c','a','b'));
INSERT INTO enum_list (enum_field)
VALUES('c'),('a'),('c'),('b');
SELECT * FROM enum_list
ORDER BY enum_field;
+------------+
| enum_field |
+------------+
| c |
| c |
| a |
| b |
+------------+
SELECT * FROM enum_list
ORDER BY CAST(enum_field AS CHAR);
+------------+
| enum_field |
+------------+
| a |
| b |
| c |
| c |
+------------+
从MariaDB 5.5.31开始，以下内容将触发警告，因为x'aa'和'X'aa'不再表现为数字。以前，在所有版本的MySQL中，均不会触发警告，因为它们的确错误地表现为数字：
SELECT CAST(0xAA AS UNSIGNED), CAST(x'aa' AS UNSIGNED), CAST(X'aa' AS UNSIGNED);
+------------------------+-------------------------+-------------------------+
| CAST(0xAA AS UNSIGNED) | CAST(x'aa' AS UNSIGNED) | CAST(X'aa' AS UNSIGNED) |
+------------------------+-------------------------+-------------------------+
| 170 | 0 | 0 |
+------------------------+-------------------------+-------------------------+
1 row in set, 2 warnings (0.00 sec)
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
Warning (Code 1292): Truncated incorrect INTEGER value: '\xAA'
强制转换：
SELECT CAST(2019-01-04 INTERVAL AS DAY_SECOND(2)) AS "Cast";
+-------------+
| Cast |
+-------------+
| 00:20:17.00 |
+-------------+
最新文章
- localForage——轻松实现 Web 离线存储（转）
- Android之自定义ViewPager实现图片的无线轮播
- Android IOS WebRTC 音视频开发总结（八十一）-- WebRTC靠谱吗？有没有适合的SDK推荐？
- C3P0连接池连接MySQL出现的问题
- WPF TreeView绑定字典集合
- ARC机制下组合关系
- Java内存模型---并发编程网 - ifeve.com
- linq 多条件查询 where 拼接+分页
- Oracle数据库中将一个数据库中一张表的数据导入到另外一张表
- SELinux 与强制访问控制系统
- luogu3320 寻宝游戏 (dfs序+倍增lca+set)
- CentOS安装中文支持包
- tomcat加载web项目报错：bad major version at offset=6
- 【DB2】查看表空间对应的物理文件地址
- IBM ILOG JViews Charts 产品及功能介绍
- git bash 出现vim弹框的时候怎么退出
- 【转】java中&和&&的区别和联系
- 浅析HTTPS与SSL原理
- ubuntu16.04 安装python3.6
- LeetCode解题报告—— Sum Root to Leaf Numbers & Surrounded Regions & Single Number II