网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts盘古助手
华为云Astro大屏应用
计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
云手机服务器 CPH
专属主机 DeH
弹性伸缩 AS
镜像服务 IMS
函数工作流 FunctionGraph
云耀云服务器(旧版)
VR云渲游平台 CVR
Huawei Cloud EulerOS
云化数据中心 CloudDC
网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
CDN与智能边缘
内容分发网络 CDN
智能边缘云 IEC
智能边缘平台 IEF
CloudPond云服务
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
可信智能计算服务 TICS
推荐系统 RES
云搜索服务 CSS
数据可视化 DLV
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
湖仓构建 LakeFormation
智能数据洞察 DataArts Insight
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
开天aPaaS
应用平台 AppStage
开天企业工作台 MSSE
开天集成工作台 MSSI
API中心 API Hub
云消息服务 KooMessage
交换数据空间 EDS
云地图服务 KooMap
云手机服务 KooPhone
组织成员账号 OrgID
云空间服务 KooDrive
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
区块链
区块链服务 BCS
数字资产链 DAC
华为云区块链引擎服务 HBS
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
价格
成本优化最佳实践
专属云商业逻辑
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
其他
管理控制台
消息中心
产品价格详情
系统权限
客户关联华为云合作伙伴须知
公共问题
宽限期保留期
奖励推广计划
活动
云服务信任体系能力说明
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts盘古助手
华为云Astro大屏应用
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
存储容灾服务 SDRS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
云存储网关 CSG
专属分布式存储服务 DSS
数据工坊 DWR
地图数据 MapDS
键值存储服务 KVS
容器
云容器引擎 CCE
云容器实例 CCI
容器镜像服务 SWR
云原生服务中心 OSC
应用服务网格 ASM
华为云UCS
数据库
云数据库 RDS
数据复制服务 DRS
文档数据库服务 DDS
分布式数据库中间件 DDM
云数据库 GaussDB
云数据库 GeminiDB
数据管理服务 DAS
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
AI开发平台ModelArts
华为HiLens
图引擎服务 GES
图像识别 Image
文字识别 OCR
自然语言处理 NLP
内容审核 Moderation
图像搜索 ImageSearch
医疗智能体 EIHealth
企业级AI应用开发专业套件 ModelArts Pro
人脸识别服务 FRS
对话机器人服务 CBS
语音交互服务 SIS
人证核身服务 IVS
视频智能分析服务 VIAS
城市智能体
自动驾驶云服务 Octopus
盘古大模型 PanguLargeModels
IoT物联网
设备接入 IoTDA
全球SIM联接 GSL
IoT数据分析 IoTA
路网数字化服务 DRIS
IoT边缘 IoTEdge
设备发放 IoTDP
企业应用
域名注册服务 Domains
云解析服务 DNS
企业门户 EWP
ICP备案
商标注册
华为云WeLink
华为云会议 Meeting
隐私保护通话 PrivateNumber
语音通话 VoiceCall
消息&短信 MSGSMS
云管理网络
SD-WAN 云服务
边缘数据中心管理 EDCM
云桌面 Workspace
应用与数据集成平台 ROMA Connect
ROMA资产中心 ROMA Exchange
API全生命周期管理 ROMA API
政企自服务管理 ESM
视频
实时音视频 SparkRTC
视频直播 Live
视频点播 VOD
媒体处理 MPC
视频接入服务 VIS
数字内容生产线 MetaStudio
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
专属云
专属计算集群 DCC
开发者工具
SDK开发指南
API签名指南
DevStar
华为云命令行工具服务 KooCLI
Huawei Cloud Toolkit
CodeArts API
云化转型
云架构中心
云采用框架
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
客户运营能力
国际站常见问题
支持计划
专业服务
合作伙伴支持计划
我的凭证
华为云公共事业服务云平台
工业软件
工业数字模型驱动引擎
硬件开发工具链平台云服务
工业数据转换引擎云服务

数据类型转换

更新时间:2024-12-04 GMT+08:00
分享

不同的数据类型之间支持转换。有如下场景涉及到数据类型转换:

  • 操作符(比较操作符、运算操作符等)的操作数的数据类型不一致。常见于查询条件或者关联条件中的比较运算。
  • 函数调用时实参和形参的数据类型不一致。
  • DML语句要更新(包括INSERT、UPDATE、MERGE、REPLACE等)的目标列,数据的类型和列的定义类型不一致。
  • 显式的类型转换:CAST(expr AS datatype),将expr表达式类型转换为datatype类型。
  • 集合运算(UNION、MINUS、EXCEPT、INTERSECT)确定最终投影列的目标数据类型后,各个SELECT查询的投影列的类型和目标数据类型不一致。
  • 其他表达式计算场景,根据不同表达式的数据类型, 来决定用于比较或者最终结果的目标数据类型。
  • 普通的字符串类型当字符序为BINARY时,将转换成对应的二进制类型(TEXT转换成BLOB,VARCHAR转换成VARBINARY等)。

数据类型转换差异点主要分为:隐式转换,显式转换、UNION/CASE、decimal类型。

隐式类型转换差异点

  • GaussDB中统一平铺成小类型到小类型的转换规则,MySQL中使用小类型转大类型,大类型转小类型的转换规则。
  • GaussDB中隐式转换因数据类型本身差异点,输出格式存在部分行为不一致。
  • GaussDB中的隐式转换,BIT数据类型到字符数据类型和二进制数据类型转换,输出存在部分行为不一致。GaussDB输出为十六进制,MySQL中根据ASCII码表转义,无法转义的输出为空。

    示例:

    m_db=# CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    ) DISTRIBUTE BY REPLICATION;
    m_db=# CREATE TABLE string_storage (
    	VS_COL1 BLOB,
    	VS_COL2 TINYBLOB,
    	VS_COL3 MEDIUMBLOB,
    	VS_COL4 LONGBLOB,
    	VS_COL5 TEXT,
    	VS_COL6 TINYTEXT,
    	VS_COL7 MEDIUMTEXT,
    	VS_COL8 LONGTEXT
    ) DISTRIBUTE BY REPLICATION;
    m_db=# INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
    m_db=# INSERT INTO string_storage SELECT * FROM bit_storage;
    m_db=# SELECT * FROM string_storage;
     VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 
    ---------+---------+---------+---------+---------+---------+---------+---------
     \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05    | \x05
    (1 row)
    m_db=# DROP TABLE bit_storage, string_storage;
    
    mysql> CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    );
    mysql> CREATE TABLE bit_storage (
    	VS_COL1 BIT(4),
    	VS_COL2 BIT(4),
    	VS_COL3 BIT(4),
    	VS_COL4 BIT(4),
    	VS_COL5 BIT(4),
    	VS_COL6 BIT(4),
    	VS_COL7 BIT(4),
    	VS_COL8 BIT(4)
    );
    mysql> INSERT INTO bit_storage VALUES(B'101', B'101', B'101', B'101', B'101', B'101', B'101', B'101');
    mysql> INSERT INTO string_storage SELECT * FROM bit_storage;
    mysql> SELECT * FROM string_storage;
    +---------+---------+---------+---------+---------+---------+---------+---------+
    | VS_COL1 | VS_COL2 | VS_COL3 | VS_COL4 | VS_COL5 | VS_COL6 | VS_COL7 | VS_COL8 |
    +---------+---------+---------+---------+---------+---------+---------+---------+
    |        |        |        |        |        |        |        |        |
    +---------+---------+---------+---------+---------+---------+---------+---------+
    1 row in set (0.00 sec)
    mysql> DROP TABLE bit_storage, string_storage;
  • WHERE子句中只带有普通字符串,GaussDB中't'、'true'、'yes'、 'y'、'on'返回TRUE,'no'、'f'、'off'、'false'、'n'返回FALSE,其余字符串报错。MySQL通过字符串转换为INT1判断返回TRUE/FALSE。

    示例:

    m_db=# CREATE TABLE test_where (
            A INT
    );
    m_db=# INSERT INTO test_where VALUES (1);
    m_db=# SELECT * FROM test_where WHERE '111';
    ERROR:  invalid input syntax for type boolean: "111"
    LINE 1: SELECT * FROM test_where WHERE '111';
    m_db=# DROP TABLE test_where;
    
    mysql> CREATE TABLE test_where (
            A INT
    );
    mysql> INSERT INTO test_where VALUES (1);
    mysql> SELECT * FROM test_where WHERE '111';
    +------+
    | a    |
    +------+
    |    1 |
    +------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE test_where;
  • 对于YEAR类型的输入,在将字符串转换为整型的过程中,MySQL考虑科学计数法,GaussDB暂不支持,统一做截断处理。

    示例:

    m_db=# CREATE TABLE test_year (
            A YEAR
    );
    m_db=# SET sql_mode = '';
    m_db=# INSERT INTO test_year VALUES ('2E3x');
    WARNING:  Data truncated for column.
    LINE 1: INSERT INTO test_year VALUES ('2E3x');
                                   ^
    CONTEXT:  referenced column: a
    m_db=# SELECT * FROM test_year ORDER BY A;
      a   
    ------
     2002
    (1 row)
    m_db=# DROP TABLE test_year;
    
    mysql> CREATE TABLE test_year (
            A YEAR
    );
    mysql> INSERT INTO test_year VALUES ('2E3x');
    mysql> SELECT * FROM test_year ORDER BY A;
    +------+
    | a    |
    +------+
    | 2000 |
    +------+
    1 row in set (0.01 sec)
    mysql> DROP TABLE test_year;
  • 对于UNION的CREATE TABLE AS场景,GaussDB不区分左右子节点的顺序,MySQL区分左右子节点的顺序,左右子节点互换会导致结果不同。

    示例:

    m_db=# CREATE TABLE test2(
    	F1 FLOAT,
    	I1 TINYINT,
    	I2 SMALLINT,
    	DTT1 DATETIME(6),
    	DEC3 DECIMAL(32, 15),
    	JS1 JSON,
    	D2 DOUBLE,
    	CH1 CHAR(255),
    	D3 DOUBLE,
    	TX1 TINYTEXT
    );
    m_db=# CREATE TABLE test1 SELECT DISTINCT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) a from test2 UNION ALL SELECT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) FROM test2;
    m_db=# DESC test1;
     Field | Type | Null | Key | Default | Extra 
    -------+------+------+-----+---------+-------
     a     | text | YES  |     |         | 
    (1 row)
    
    m_db=# CREATE TABLE test3 SELECT DISTINCT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) a from test2 UNION ALL SELECT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) FROM test2;
    m_db=# DESC test3;
     Field | Type | Null | Key | Default | Extra 
    -------+------+------+-----+---------+-------
     a     | text | YES  |     |         | 
    (1 row)
    
    m_db=# DROP TABLE test1, test2, test3;
    
    mysql> CREATE TABLE test2(
    	F1 FLOAT,
    	I1 TINYINT,
    	I2 SMALLINT,
    	DTT1 DATETIME(6),
    	DEC3 DECIMAL(32, 15),
    	JS1 JSON,
    	D2 DOUBLE,
    	CH1 CHAR(255),
    	D3 DOUBLE,
    	TX1 TINYTEXT
    );
    mysql> CREATE TABLE test1 SELECT DISTINCT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) a from test2 UNION ALL SELECT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) FROM test2;
    mysql> DESC test1;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | a     | varchar(53) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    mysql> CREATE TABLE test3 SELECT DISTINCT sqrt((DEC3 + DTT1 - JS1) * D3 / - TX1 % I2) a from test2 UNION ALL SELECT concat((F1 + I1 - DTT1) * DEC3 % D2 / CH1) FROM test2;
    mysql> DESC test3;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | a     | varchar(23) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    mysql> DROP TABLE test1, test2, test3;
  • GaussDB中函数嵌套场景下,涉及到聚合函数(如max、min、sum和avg)中存在字符串类型包含非数值字符,隐式转换到数值类型发生截断或置零,且包含操作符比较、having比较的场景时,GaussDB统一进行类型转换并产生告警,MySQL在相同场景下不会全部产生告警。

    示例:

    m_db=# SET m_format_behavior_compat_options= 'enable_precision_decimal';
    SET
    m_db=# SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:02.132'
     ?column?
    ----------
     t
    (1 row)
    
    m_db=# SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:02.132'
     ?column?
    ----------
     t
    (1 row)
    
    m_db=# SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
    m_db(# WHERE EXISTS (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
    m_db(# GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
    CONTEXT:  referenced column: col5
                col5
    ----------------------------
     2006-04-27 20:19:41.352000
    (1 row)
    
    m_db=# SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
    m_db(# WHERE EXISTS (SELECT sum(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
    m_db(# GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
    CONTEXT:  referenced column: col5
    WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
    CONTEXT:  referenced column: col5
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
    CONTEXT:  referenced column: col5
    WARNING:  Truncated incorrect double value: '2006-04-27 20:19:08.132'
    CONTEXT:  referenced column: col5
    WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
    CONTEXT:  referenced column: col5
    WARNING:  Truncated incorrect double value: '1985-09-01 07:59:59'
    CONTEXT:  referenced column: col5
    WARNING:  Incorrect datetime value: '3991'
    CONTEXT:  referenced column: col5
     col5
    ------
    
    (1 row)
    
    mysql> SELECT max(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
    +--------------+
    | max(c4) <> 0 |
    +--------------+
    |            1 |
    +--------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT sum(c4) <> 0 FROM ((SELECT 2.22 id, '2006-04-27 20:19:02.132' c4)) tb_1;
    +--------------+
    | sum(c4) <> 0 |
    +--------------+
    |            1 |
    +--------------+
    1 row in set, 1 warning (0.00 sec)
    
    mysql> SHOW warnings;
    +---------+------+-------------------------------------------------------------+
    | Level   | Code | Message                                                     |
    +---------+------+-------------------------------------------------------------+
    | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:02.132' |
    +---------+------+-------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT (SELECT max(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
        -> WHERE EXISTS (SELECT max(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
        -> GROUP BY id WITH rollup HAVING f5<>0 limit 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
    +----------------------------+
    | col5                       |
    +----------------------------+
    | 2006-04-27 20:19:41.352000 |
    +----------------------------+
    1 row in set (0.00 sec)
    
    mysql> SELECT (SELECT sum(c4) f5 FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_1
        -> WHERE EXISTS (SELECT sum(c4) FROM ((SELECT 2.22 id, '2006-04-27 20:19:08.132' c4) UNION all (SELECT 2.22 id, '1985-09-01 07:59:59' c4)) tb_2)
        -> GROUP BY id WITH rollup HAVING f5<>0 LIMIT 0,1) + INTERVAL '33.22' SECOND_MICROSECOND col5;
    +------+
    | col5 |
    +------+
    | NULL |
    +------+
    1 row in set, 7 warnings (0.01 sec)
    
    mysql> SHOW warnings;
    +---------+------+-------------------------------------------------------------+
    | Level   | Code | Message                                                     |
    +---------+------+-------------------------------------------------------------+
    | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '2006-04-27 20:19:08.132' |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
    | Warning | 1292 | Truncated incorrect DOUBLE value: '1985-09-01 07:59:59'     |
    | Warning | 1292 | Incorrect datetime value: '3991'                            |
    +---------+------+-------------------------------------------------------------+
    7 rows in set (0.00 sec)
    

显式类型转换差异点

  • GaussDB中平铺成对各目标类型的转换规则,MySQL中使用C++多态重载函数,在嵌套场景中存在不一致行为。
    示例:
    m_db=# SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
    WARNING:  Truncated incorrect INTEGER value: '2023-01-01'
    CONTEXT:  referenced column: cast
     cast 
    ------
     2023
    (1 row)
    
    mysql> SELECT CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED);
    +---------------------------------------------------------+
    | CAST(GREATEST(date'2023-01-01','2023-01-01') AS SIGNED) |
    +---------------------------------------------------------+
    |                                                20230101 |
    +---------------------------------------------------------+
  • 在GaussDB中,BLOB、TINYBLOB、MEDIUMBLOB、LONGBLOB、BINARY、VARBINARY、BIT、及YEAR类型显式转换为JSON类型,结果与MySQL不同。

    示例:

    m_db=# CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
    CREATE TABLE
    m_db=# INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
    INSERT 0 1
    m_db=# SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
          CAST       |  CAST  | CAST  |               CAST                |                 CAST                 |               CAST                | CAST |  CAST  
    -----------------+--------+-------+-----------------------------------+--------------------------------------+-----------------------------------+------+--------
     "[1, \"json\"]" | "true" | "abc" | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "[1, \"json\"]                     " | "{\"jsnid\": 1, \"tag\": \"ab\"}" | "20" | "2020"
    (1 row)
    
    mysql> CREATE TABLE test_blob (c1 BLOB, c2 TINYBLOB, c3 MEDIUMBLOB, c4 LONGBLOB, c5 BINARY(32), c6 VARBINARY(100), c7 BIT(64), c8 YEAR);
    Query OK, 0 rows affected (0.02 sec)
    mysql> INSERT INTO test_blob VALUES('[1, "json"]', 'true', 'abc', '{"jsnid": 1, "tag": "ab"}', '[1, "json"]', '{"jsnid": 1, "tag": "ab"}', '20', '2020');
    Query OK, 1 row affected (0.00 sec)
    mysql> SELECT CAST(c1 AS JSON), CAST(c2 AS JSON), CAST(c3 AS JSON), CAST(c4 AS JSON), CAST(c5 AS JSON), CAST(c6 AS JSON), CAST(c7 AS JSON), CAST(c8 AS JSON) FROM test_blob;
    +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
    | CAST(c1 AS JSON)                  | CAST(c2 AS JSON)          | CAST(c3 AS JSON)      | CAST(c4 AS JSON)                                      | CAST(c5 AS JSON)                                              | CAST(c6 AS JSON)                                     | CAST(c7 AS JSON)             | CAST(c8 AS JSON)         |
    +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
    | "base64:type252:WzEsICJqc29uIl0=" | "base64:type249:dHJ1ZQ==" | "base64:type250:YWJj" | "base64:type251:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type254:WzEsICJqc29uIl0AAAAAAAAAAAAAAAAAAAAAAAAAAAA=" | "base64:type15:eyJqc25pZCI6IDEsICJ0YWciOiAiYWIifQ==" | "base64:type16:AAAAAAAAMjA=" | "base64:type13:MjAyMA==" |
    +-----------------------------------+---------------------------+-----------------------+-------------------------------------------------------+---------------------------------------------------------------+------------------------------------------------------+------------------------------+--------------------------+
    1 row in set (0.00 sec)
  • GaussDB在JSON数据类型显式转换后运用于精度计算时,与MySQL 5.7不一致,与MySQL 8.0一致,计算时精度与使用JSON类型表中数据精度保持一致。

    示例:

    test=# DROP TABLE tt01;
    DROP TABLE
    test=# CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1;
    INSERT 0 1
    test=# DESC tt01;
     Field |  Type  | Null | Key | Default | Extra 
    -------+--------+------+-----+---------+-------
     c1    | double | YES  |     |         | 
    (1 row)
    
    test=# SELECT * FROM tt01;
         c1      
    -------------
     -98.7654321
    (1 row)
    
    mysql> SELECT version();
    +------------------+
    | version()        |
    +------------------+
    | 5.7.44-debug-log |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE tt01;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1;
    Query OK, 1 row affected (0.03 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> DESC tt01;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | c1    | double(17,0) | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.00 sec)
    
    mysql> SELECT * FROM tt01;
    +------+
    | c1   |
    +------+
    |  -99 |
    +------+
    1 row in set (0.00 sec)
    
    mysql> SELECT version();
    +--------------+
    | version()    |
    +--------------+
    | 8.0.36-debug |
    +--------------+
    1 row in set (0.00 sec)
    
    mysql> DROP TABLE tt01;
    Query OK, 0 rows affected (0.05 sec)
    
    mysql> CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1;
    Query OK, 1 row affected (0.12 sec)
    Records: 1  Duplicates: 0  Warnings: 0
    
    mysql> DESC tt01;
    +-------+--------+------+-----+---------+-------+
    | Field | Type   | Null | Key | Default | Extra |
    +-------+--------+------+-----+---------+-------+
    | c1    | double | YES  |     | NULL    |       |
    +-------+--------+------+-----+---------+-------+
    1 row in set (0.01 sec)
    
    mysql> SELECT * FROM tt01;
    +-------------+
    | c1          |
    +-------------+
    | -98.7654321 |
    +-------------+
    1 row in set (0.00 sec)

UNION,CASE和相关构造差异点

  • POLYGON + NULL、POINT + NULL、POLYGON + POINT组合在MySQL中均返回GEOMETRY类型,GaussDB中未涉及,暂时当做报错处理。
  • SET和ENUM两种类型暂未支持,暂时当做报错处理。
  • JSON和二进制类型(BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB)的UNION和UNION ALL组合,MySQL中返回LONGBLOB类型,GaussDB中返回JSON类型,同时支持二进制类型(BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB)到JSON的隐式类型转换。
  • 未设置m_format_behavior_compat_options为enable_precision_decimal时,常量类型和其他类型做类型聚合的时候,输出类型的精度为其他类型的精度。如“SELECT "helloworld" UNION SELECT p FROM t;”的结果的精度为属性p的精度。
  • 未设置m_format_behavior_compat_options为enable_precision_decimal时,定点常量和不带精度约束的类型(非字符串类型如int、bool、year等,聚合结果类型为定点类型)聚合时,精度约束会按照定点数默认精度31输出。
  • merge rule差异:

    MySQL 5.7中存在部分不合理的类型推导,如BIT类型和整型/YEAR类型推导会得出VARBINARY类型,UNSIGNED类型和非UNSIGNED类型推导会得到带UNSIGNED的类型等,同时CASE WHEN和UNION的聚合结果也存在差异,类型推导结果太小时存在数据溢出风险。在MySQL 8.0版本修复了上述相关的问题,因此merge rule聚合规则以8.0为准。

  • MySQL中BINARY和CHAR填充字符不相同,BINARY填充'\0',CHAR填充空格,GaussDB中BINARY和CHAR都是填充空格。
  • 在精度传递场景下,使用CASE WHEN语句时,会进行类型转换和精度重新计算,导致最终的输出结果与CASE子句对比会出现末尾多零场景或末尾少零场景:
    • 末尾多零场景:CASE节点会根据CASE子句的精度计算CASE节点精度,当THEN子句的精度比CASE节点的精度小时,会在CASE节点末尾补零。
    • 末尾少零场景:多层CASE WHEN嵌套时,内层CASE执行类型转换之后,只保留内层CASE的精度,外层CASE无法得到THEN子句的精度信息,因此外层CASE会根据内层CASE的精度计算的精度进行类型转换。当外层CASE类型转换时内层CASE精度比THEN子句少,会出现末尾少零场景。

    示例:

    • 末尾多零少零场景。
      -- 末尾多零场景。
      m_db=# SELECT 15.6 AS result;
       result 
      --------
         15.6
      (1 row)
      
      m_db=# SELECT CASE WHEN 1 < 2 THEN 15.6 ELSE  23.578 END AS result;
       result 
      --------
       15.600
      (1 row)
      
      m_db=# SELECT  greatest(12, 3.4, 15.6) AS result;
       result 
      --------
         15.6
      (1 row)
      
      m_db=# SELECT CASE WHEN 1 < 2 THEN greatest(12, 3.4, 15.6) ELSE greatest(123.4, 23.578, 36) END AS result;
       result 
      --------
       15.600
      (1 row)
      
      -- 末尾少零场景。
      m_db=# CREATE TABLE t1 AS SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
      INSERT 0  1
      m_db=# DESC t1;
       Field  |    Type     | Null | Key | Default | Extra 
      --------+-------------+------+-----+---------+-------
       result | double(8,7) | YES  |     |         | 
      (1 row)
      
      m_db=# SELECT (false/-timestamp '2008-12-31 23:59:59.678') AS result;
         result   
      ------------
       -0.0000000
      (1 row)
      
      m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result;
      INSERT 0 1
      m_db=# DESC t1;
       Field  |  Type  | Null | Key | Default | Extra 
      --------+--------+------+-----+---------+-------
       result | double | YES  |     |         | 
      (1 row)
      
      m_db=# SELECT (CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END) AS result;
       result 
      --------
           -0
      (1 row)
      
      m_db=# DROP TABLE t1;
      DROP TABLE
      m_db=# CREATE TABLE t1 AS SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
      INSERT 0 1
      m_db=# desc t1;
       Field  |    Type     | Null | Key | Default | Extra 
      --------+-------------+------+-----+---------+-------
       result | varchar(23) | YES  |     |         | 
      (1 row)
      
      m_db=# SELECT (CASE WHEN 1+1=2 THEN CASE WHEN 1<2 THEN false/-timestamp '2008-12-31 23:59:59.678' ELSE 0016.11e3/'22.2' END ELSE 'test' END) AS result;
       result 
      --------
       -0
      (1 row)
    • 在开启精度传递的场景下,使用集合运算(UNION、MINUS、EXCEPT、INTERSECT),如果参与集合运算的查询语句,其查询的字段为函数、表达式而不是直接使用表中的字段,且查询的结果数据类型为INT/INT UNSIGNED,则最后返回的数据类型存在差异。在MySQL中,返回的数据类型为BIGINT/BIGINT UNSIGNED;在GaussDB中,返回的数据类型为INT/INT UNSIGNED。
      -- GaussDB执行结果。
      m_db=# SET m_format_behavior_compat_options='select_column_name,enable_precision_decimal';
      SET
      m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
      DROP TABLE
      m_db=# CREATE TABLE t1(a INT, b INT);
      CREATE TABLE
      m_db=# CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
      CREATE TABLE
      m_db=# CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
      INSERT 0 0
      m_db=# DESC ctas1;
       Field  |    Type     | Null | Key | Default | Extra 
      --------+-------------+------+-----+---------+-------
       a      | integer(11) | YES  |     |         | 
       ABS(a) | integer(11) | YES  |     |         | 
      (2 rows)
      
      m_db=# CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
      INSERT 0 0
      m_db=# DESC ctas2;
       Field  |         Type         | Null | Key | Default | Extra 
      --------+----------------------+------+-----+---------+-------
       c      | integer(11) unsigned | YES  |     |         | 
       ABS(c) | integer(11) unsigned | YES  |     |         | 
      (2 rows)
      
      m_db=# DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
      DROP TABLE
      
      -- MySQL执行结果。
      mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
      Query OK, 0 rows affected, 4 warnings (0.00 sec)
      
      mysql> CREATE TABLE t1(a INT, b INT);
      Query OK, 0 rows affected (0.05 sec)
      
      mysql> CREATE TABLE t2(c INT UNSIGNED, d INT UNSIGNED);
      Query OK, 0 rows affected (0.03 sec)
      
      mysql> CREATE TABLE ctas1 AS (SELECT a, ABS(a) FROM t1) UNION (SELECT b, ABS(b) FROM t1);
      Query OK, 0 rows affected (0.03 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC ctas1;
      +--------+------------+------+-----+---------+-------+
      | Field  | Type       | Null | Key | Default | Extra |
      +--------+------------+------+-----+---------+-------+
      | a      | int(11)    | YES  |     | NULL    |       |
      | ABS(a) | bigint(20) | YES  |     | NULL    |       |
      +--------+------------+------+-----+---------+-------+
      2 rows in set (0.01 sec)
      
      mysql> CREATE TABLE ctas2 AS (SELECT c, ABS(c) FROM t2) UNION (SELECT d, ABS(d) FROM t2);
      Query OK, 0 rows affected (0.05 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC ctas2;
      +--------+---------------------+------+-----+---------+-------+
      | Field  | Type                | Null | Key | Default | Extra |
      +--------+---------------------+------+-----+---------+-------+
      | c      | int(11) unsigned    | YES  |     | NULL    |       |
      | ABS(c) | bigint(20) unsigned | YES  |     | NULL    |       |
      +--------+---------------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      
      mysql> DROP TABLE IF EXISTS t1,t2,ctas1,ctas2;
      Query OK, 0 rows affected (0.07 sec)
    • 在开启精度传递的场景下,CASE WHEN被嵌套场景的结果与MySQL保持差异。MySQL的类型可以透过多层直接转换,而GaussDB结果精度是逐层确定并且逐层转换的,因此可能导致结果小数位或进位和MySQL不一致。
      -- GaussDB:
      m_db=# SET m_format_behavior_compat_options='enable_precision_decimal';
      SET
      m_db=# SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
               res
      ---------------------
       -1.8559999999974321
      (1 row)
      
      -- MySQL:
      mysql> SELECT (CASE WHEN 1+1=3 THEN 'test' ELSE CASE WHEN 1>2 THEN '-1.5'%06.6600e1 ELSE -TIME '10:10:10.456'%2.2 END END) RES;
      +--------+
      | res    |
      +--------+
      | -1.856 |
      +--------+
      1 row in set (0.00 sec)
    • 对于需要int类型的运算符(如 ~,&,|,<<,>>)嵌套CASE WHEN语句,若CASE WHEN语句返回的是varchar类型,则实际情况可以会发生截断(根据原表数据分析是否会发生截断),GaussDB会报出相应错误(SELECT查询warning告警,CREATE建表error报错),MySQL不会报错。若GaussDB想要完成CREATE TABLE建表操作,可以通过设置sql_mode关闭严格模式。
      -- GaussDB:
      m_db=# CREATE TABLE t_base (num_var numeric(20, 10), time_var time(6));
      CREATE TABLE
      m_db=# INSERT INTO t_base VALUES ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
      INSERT 0 2
      m_db=# SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base;
      WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
      CONTEXT:  referenced column: res2
      WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
      CONTEXT:  referenced column: res2
       res2 
      ------
       2513
       416
      (2 rows)
      m_db=# CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base;
      ERROR:  Truncated incorrect INTEGER value: '-2514.1441000000'
      CONTEXT:  referenced column: res2
      m_db=# SET sql_mode="";
      SET
      m_db=# CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base;
      WARNING:  Truncated incorrect INTEGER value: '-2514.1441000000'
      CONTEXT:  referenced column: res2
      WARNING:  Truncated incorrect INTEGER value: '-417.2147000000'
      CONTEXT:  referenced column: res2
      INSERT 0 2
      m_db=# DESC t1;
       Field |        Type         | Null | Key | Default | Extra 
      -------+---------------------+------+-----+---------+-------
       res2  | bigint(21) unsigned | YES  |     |         | 
      (1 row)
      
      -- MySQL:
      mysql> CREATE TABLE t_base (num_var numeric(20, 10), time_var time(6));
      Query OK, 0 rows affected (0.01 sec)
      mysql> INSERT INTO t_base VALUES ('-2514.1441000000','12:10:10.125000'),('-417.2147000000',' 11:30:25.258000');
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      mysql> SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base;
      +------+
      | res2 |
      +------+
      | 2513 |
      |  416 |
      +------+
      2 rows in set (0.00 sec)
      mysql> CREATE TABLE t1 AS SELECT (~(CASE WHEN false THEN time_var ELSE num_var END)) AS res2 FROM t_base;
      Query OK, 2 rows affected (0.01 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      
      mysql> DESC t1;
      +-------+---------------------+------+-----+---------+-------+
      | Field | Type                | Null | Key | Default | Extra |
      +-------+---------------------+------+-----+---------+-------+
      | res2  | bigint(21) unsigned | YES  |     | NULL    |       |
      +-------+---------------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
    • 在开启精度传递的场景下,对于CREATE VIEW AS SELECT CASE WHEN语句和SELECT CASE WHEN语句嵌套常量(包括常量计算、函数嵌套常量等)的情况,GaussDB在该情况下值保持一致,MySQL在SELECT CASE WHEN语句中可能会丢失部分精度。
      -- GaussDB:
      m_db=# CREATE OR REPLACE VIEW test_view AS
      m_db-# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
      CREATE VIEW
      m_db=# SELECT * FROM test_view;
           c1     |    c2     
      ------------+-----------
       0.74663677 | 0.7466368
      (1 row)
      m_db=# SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
           c1     |    c2     
      ------------+-----------
       0.74663677 | 0.7466368
      (1 row)
      
      -- MySQL:
      mysql> CREATE OR REPLACE VIEW test_view AS
          -> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
      Query OK, 0 rows affected (0.00 sec)
      mysql> SELECT * FROM test_view;
      +------------+-----------+
      | c1         | c2        |
      +------------+-----------+
      | 0.74663677 | 0.7466368 |
      +------------+-----------+
      1 row in set (0.00 sec)
      mysql> SELECT (CASE WHEN 1<2 THEN 3.33/4.46 ELSE 003.3630/002.2600 END) c1,(CASE WHEN 1>2 THEN IFNULL(null,3.363/2.2) ELSE NULLIF(3.33/4.46,3.363/2.2) END) c2;
      +----------+----------+
      | c1       | c2       |
      +----------+----------+
      | 0.746637 | 0.746637 |
      +----------+----------+
      1 row in set (0.00 sec)
    • 在开启精度传递的场景下,M-Compatibility模式数据库支持UNION/CASE WHEN语句建表,但是由于架构不同,M-Compatibility模式数据库无法保证创建的表的所有类型与MySQL 8.0完全相同。MySQL返回字符串、二进制相关类型的场景,以及部分函数嵌套场景,与GaussDB存在不一致。
      -- GaussDB:
      m_db=# CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
      CREATE TABLE
      m_db=# CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 ELSE 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
      INSERT 0 0
      m_db=# DESC t1;
      Field |      Type      | Null | Key | Default | Extra
      -------+----------------+------+-----+---------+-------
      id    | integer(11)    | YES  |     |         |
      f35   | varchar(255)   | YES  |     |         |
      f36   | mediumtext     | YES  |     |         |
      f41   | varbinary(255) | YES  |     |         |
      f42   | blob           | YES  |     |         |
      f43   | mediumblob     | YES  |     |         |
      f44   | longblob       | YES  |     |         |
      (7 rows)
      
      m_db=# CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
      CREATE TABLE
      m_db=# CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
      CREATE TABLE
      m_db=# CREATE TABLE testtext AS (SELECT * FROM testtext1) UNION (SELECT * FROM testtext2);
      CREATE TABLE
      m_db=# DESC testtext;
      m_db=# 
       Field | Type | Null | Key | Default | Extra 
      -------+------+------+-----+---------+-------
       col10 | text | YES  |     |         | 
      (1 row)
      
      m_db=# CREATE TABLE testchar AS SELECT (SELECT lcase(-6873.4354)) a, (SELECT sec_to_time(-485769.567)) b UNION ALL SELECT (SELECT bin(-58768923.21321)), (SELECT asin(-0.7237465));
      INSERT 0 2
      m_db=# desc testchar;
       Field |    Type     | Null | Key | Default | Extra 
      -------+-------------+------+-----+---------+-------
       a     | text        | YES  |     |         | 
       b     | varchar(23) | YES  |     |         | 
      (2 rows)
      
      m_db=# CREATE TABLE test_func (col_text char(29));
      CREATE TABLE
      m_db=# CREATE TABLE test1 AS SELECT * FROM ( SELECT 
              GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
              ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
              SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
              DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
              DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
              ADDTIME(col_text, '8:20:20.3554') f7,
              SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
      UNION ALL 
              SELECT * FROM ( SELECT
              GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
              ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
              SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
              DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
              DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
              ADDTIME(col_text, '8:20:20.3554') f7,
              SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
      INSERT 0 0
      m_db=# DESC test1;
       Field |    Type     | Null | Key | Default | Extra 
      -------+-------------+------+-----+---------+-------
       f1    | double      | YES  |     |         | 
       f2    | double      | YES  |     |         | 
       f3    | varchar(29) | YES  |     |         | 
       f4    | varchar(29) | YES  |     |         | 
       f5    | varchar(29) | YES  |     |         | 
       f6    | varchar(29) | YES  |     |         | 
       f7    | varchar(29) | YES  |     |         | 
       f8    | varchar(29) | YES  |     |         | 
      (8 rows)
      
      -- MySQL:
      mysql> CREATE TABLE IF NOT EXISTS testcase (id int, col_text1 tinytext, col_text2 text, col_blob1 tinyblob, col_blob2 blob, col_blob3 mediumblob, col_blob4 longblob);
      Query OK, 0 rows affected (0.01 sec)
      mysql> CREATE TABLE t1 AS SELECT id,(CASE WHEN id=2 THEN col_text1 ELSE 'test' END) f35,  (CASE WHEN id=2 THEN col_text2 ELSE 'test' END) f36,(CASE WHEN id=2 THEN col_blob1 ELSE 'test' END) f41,  (CASE WHEN id=2 THEN col_blob2 ELSE 'test' END) f42,  (CASE WHEN id=2 THEN col_blob3 else 'test' END) f43,  (CASE WHEN id=2 THEN col_blob4 ELSE 'test' END) f44 FROM testcase;
      Query OK, 0 rows affected (0.00 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC t1;
      +-------+----------+------+-----+---------+-------+
      | Field | Type     | Null | Key | Default | Extra |
      +-------+----------+------+-----+---------+-------+
      | id    | int      | YES  |     | NULL    |       |
      | f35   | longtext | YES  |     | NULL    |       |
      | f36   | longtext | YES  |     | NULL    |       |
      | f41   | longblob | YES  |     | NULL    |       |
      | f42   | longblob | YES  |     | NULL    |       |
      | f43   | longblob | YES  |     | NULL    |       |
      | f44   | longblob | YES  |     | NULL    |       |
      +-------+----------+------+-----+---------+-------+
      7 rows in set (0.00 sec)
      
      mysql> CREATE TABLE IF NOT EXISTS testtext1 (col10 text);
      Query OK, 0 rows affected (0.01 sec)
      
      mysql> CREATE TABLE IF NOT EXISTS testtext2 (col10 text);
      Query OK, 0 rows affected (0.02 sec)
      
      mysql>  CREATE TABLE testtext AS (SELECT * FROM testtext1) UNION (SELECT * FROM testtext2);
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC testtext;
      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | col10 | mediumtext | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      
      mysql> SET sql_mode='';
      Query OK, 0 rows affected, 1 warning (0.01 sec)
      
      mysql> CREATE TABLE testchar AS SELECT (SELECT lcase(-6873.4354)) a, (SELECT sec_to_time(-485769.567)) b UNION ALL SELECT (SELECT bin(-58768923.21321)), (SELECT asin(-0.7237465));
      Query OK, 2 rows affected, 1 warning (0.02 sec)
      Records: 2  Duplicates: 0  Warnings: 1
      
      mysql> DESC testchar;
      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | a     | varchar(21) | YES  |     | NULL    |       |
      | b     | varchar(53) | YES  |     | NULL    |       |
      +-------+-------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      
      mysql> CREATE TABLE test_func (col_text char(29));
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE test1 AS SELECT * FROM ( SELECT 
          -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
          -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
          -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
          -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
          -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
          -> ADDTIME(col_text, '8:20:20.3554') f7,
          -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
          -> UNION ALL 
          -> SELECT * FROM ( SELECT
          -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
          -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
          -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
          -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
          -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
          -> ADDTIME(col_text, '8:20:20.3554') f7,
          -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
          -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t1
          -> UNION ALL 
          -> SELECT * FROM ( SELECT
          -> GREATEST(2.22, col_text) f1, LEAST(2.22, col_text) f2,
          -> ADDDATE(col_text, INTERVAL '1.28.16.31' HOUR_MICROSECOND) f3,
          -> SUBDATE(col_text, INTERVAL '39.49.15' MINUTE_MICROSECOND) f4,
          -> DATE_SUB(col_text, INTERVAL '45' MICROSECOND) f5,
          -> DATE_ADD(col_text, INTERVAL '12.00.00.00.001' DAY_MICROSECOND) f6,
          -> ADDTIME(col_text, '8:20:20.3554') f7,
          -> SUBTIME(col_text, '8:20:20.3554') f8 from test_func) t2;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> 
      mysql> DESC test1;
      +-------+------------+------+-----+---------+-------+
      | Field | Type       | Null | Key | Default | Extra |
      +-------+------------+------+-----+---------+-------+
      | f1    | binary(23) | YES  |     | NULL    |       |
      | f2    | binary(23) | YES  |     | NULL    |       |
      | f3    | char(29)   | YES  |     | NULL    |       |
      | f4    | char(29)   | YES  |     | NULL    |       |
      | f5    | char(29)   | YES  |     | NULL    |       |
      | f6    | char(29)   | YES  |     | NULL    |       |
      | f7    | char(29)   | YES  |     | NULL    |       |
      | f8    | char(29)   | YES  |     | NULL    |       |
      +-------+------------+------+-----+---------+-------+
      8 rows in set (0.01 sec)
    • 在开启精度传递的场景下,对于CREATE TABLE AS SELECT A % (CASE WHEN)语句,如果A是DECIMAL类型,CASE WHEN结果为日期类型(DATE、TIME、DATETIME),两者进行取模运算(%)得到的精度保持差异。GaussDB得到的精度跟decimal类型与日期类型直接取模运算得到的精度保持一致。
      -- GaussDB:(decimal % date类型case)与(numeric%date), 精度一致,都是decimal(24,10)。
      m_db=# SET m_format_behavior_compat_options = 'enable_precision_decimal';
      SET
      m_db=# DROP TABLE IF EXISTS t1, t2;
      DROP TABLE
      m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      CREATE TABLE
      m_db=# CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
      INSERT 0 0
      m_db=# DESC t2;
       Field |      Type      | Null | Key | Default | Extra
      -------+----------------+------+-----+---------+-------
       res1  | decimal(24,10) | YES  |     |         |
      (1 row)
      
      m_db=# DROP TABLE IF EXISTS t1, t2;
      DROP TABLE
      m_db=# CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      CREATE TABLE
      m_db=# CREATE TABLE t2 AS SELECT num_var % dt_var AS RES1 from t1;
      INSERT 0 0
      m_db=# DESC t2;
       Field |      Type      | Null | Key | Default | Extra
      -------+----------------+------+-----+---------+-------
       res1  | decimal(24,10) | YES  |     |         |
      (1 row)
      
      -- MySQL 5.7,精度存在差异。(decimal % date类型case)精度为decimal(65,10),(numeric%date)精度为decimal(24,10)。
      mysql> DROP TABLE IF EXISTS t1, t2;
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC t2;
      
      +-------+----------------+------+-----+---------+-------+
      | Field | Type           | Null | Key | Default | Extra |
      +-------+----------------+------+-----+---------+-------+
      | res1  | decimal(65,10) | YES  |     | NULL    |       |
      +-------+----------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      
      mysql> DROP TABLE IF EXISTS t1, t2;
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC t2;
      +-------+----------------+------+-----+---------+-------+
      | Field | Type           | Null | Key | Default | Extra |
      +-------+----------------+------+-----+---------+-------+
      | res1  | decimal(24,10) | YES  |     | NULL    |       |
      +-------+----------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      
      -- MySQL 8.0,(decimal % date类型case)和(numeric%date)精度都为decimal(20,10)。
      mysql> DROP TABLE IF EXISTS t1, t2;
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t2 AS SELECT num_var % (CASE WHEN true THEN dt_var ELSE dt_var END) AS res1 FROM t1;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC t2;
      
      +-------+----------------+------+-----+---------+-------+
      | Field | Type           | Null | Key | Default | Extra |
      +-------+----------------+------+-----+---------+-------+
      | res1  | decimal(20,10) | YES  |     | NULL    |       |
      +-------+----------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      
      mysql> DROP TABLE IF EXISTS t1, t2;
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t1 (num_var numeric(20, 10), date_var date, time_var time(6), dt_var datetime(6));
      
      Query OK, 0 rows affected (0.02 sec)
      
      mysql> CREATE TABLE t2 AS SELECT num_var % dt_var AS res1 FROM t1;
      Query OK, 0 rows affected (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 0
      
      mysql> DESC t2;
      +-------+----------------+------+-----+---------+-------+
      | Field | Type           | Null | Key | Default | Extra |
      +-------+----------------+------+-----+---------+-------+
      | res1  | decimal(20,10) | YES  |     | NULL    |       |
      +-------+----------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
    • 在开启精度传递的场景下,使用UNION,如果参与集合运算的查询语句,其查询的字段为常量,且查询的结果数据类型为INT/DECIMAL,则最后返回的精度存在差异。在MySQL 5.7中,返回的精度与UNION左右两侧的顺序有关;在MySQL 8.0中修复了这个问题,返回的精度与UNION左右两侧的顺序无关;在GaussDB中,返回的精度与UNION左右两侧的顺序无关,与MySQL 8.0一致,与MySQL 5.7不一致。
      -- GaussDB:
      m_db=# CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
      INSERT 0 2
      m_db=# DESC t1;
      Field |     Type     | Null | Key | Default | Extra
      -------+--------------+------+-----+---------+-------
      c2    | decimal(5,3) | YES  |     |         |        
      (1 row)
      m_db=# CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
      INSERT 0 2
      m_db=# DESC t2;
      Field |     Type     | Null | Key | Default | Extra
      -------+--------------+------+-----+---------+-------
      c2    | decimal(5,3) | YES  |     |         |        
      (1 row)
      
      -- MySQL 5.7:
      mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
      Query OK, 2 rows affected (2.28 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      mysql> DESC t1;
      +-------+--------------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+--------------+------+-----+---------+-------+
      | c2    | decimal(6,3) | NO   |     | 0.000   |       |
      +-------+--------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      mysql> CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);
      Query OK, 2 rows affected (2.22 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      mysql> DESC t2;
      +-------+--------------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+--------------+------+-----+---------+-------+
      | c2    | decimal(5,3) | NO   |     | 0.000   |       |
      +-------+--------------+------+-----+---------+-------+
      1 row in set (0.00 sec)
      
      -- MySQL 8.0:
      mysql> CREATE TABLE t1 AS (SELECT -23.45 c2) UNION ALL (SELECT -45.678 c2);
      Query OK, 2 rows affected (0.02 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      mysql> DESC t1;
      +-------+--------------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+--------------+------+-----+---------+-------+
      | c2    | decimal(5,3) | NO   |     | 0.000   |       |
      +-------+--------------+------+-----+---------+-------+
      1 row in set (0.03 sec)
      mysql>  CREATE TABLE t2 AS (SELECT -45.678 c2) UNION ALL (SELECT -23.45 c2);   
      Query OK, 2 rows affected (0.03 sec)
      Records: 2  Duplicates: 0  Warnings: 0
      
      mysql> DESC t2;
      +-------+--------------+------+-----+---------+-------+
      | Field | Type         | Null | Key | Default | Extra |
      +-------+--------------+------+-----+---------+-------+
      | c2    | decimal(5,3) | NO   |     | 0.000   |       |
      +-------+--------------+------+-----+---------+-------+
      1 row in set (0.02 sec)

双冒号转换差异点

GaussDB中使用双冒号将函数入参转换为期望类型可能导致结果超出预期;MySQL中无双冒号功能。

示例:
m_db=# SELECT POW("12"::VARBINARY,"12"::VARBINARY);
ERROR:  value out of range: overflow
CONTEXT:  referenced column: pow

varbinary col
m_db=# CREATE TABLE test_varbinary (
        A VARBINARY(10)
);
m_db=# INSERT INTO test_varbinary VALUES ('12');
m_db=# SELECT POW(A, A) FROM test_varbinary;
      pow      
---------------
 8916100448256
(1 row)

decimal类型差异点

在CREATE TABLE ... AS (SELECT ...)语句中,使用decimal数据类型,若含有前缀0,M-Compatibility兼容模式下忽略前缀0,长度计算不包括0,在MySQL 5.7下,长度计算加上前缀0的数量,在MySQL 8.0下,无论存在多少个前缀0,长度计算只加上1。
--GaussDB
m_db=# CREATE TABLE test AS SELECT 004.01 col1;
INSERT 0 1
m_db=# DESC test;
 Field |     Type     | Null | Key | Default | Extra 
-------+--------------+------+-----+---------+-------
 col1  | decimal(3,2) | YES  |     |         | 
(1 row)

--mysql 5.7
mysql> CREATE TABLE test AS SELECT 004.01 col1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> DESC test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(5,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

--mysql 8.0
mysql> CREATE TABLE test AS SELECT 004.01 col1;
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> DESC test;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | decimal(4,2) | NO   |     | 0.00    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)
提示

您即将访问非华为云网站,请注意账号财产安全

文档反馈

文档反馈

意见反馈

0/500

标记内容

同时提交标记内容