sql数据类型映射与数据类型转换

这篇具有很好参考价值的文章主要介绍了sql数据类型映射与数据类型转换。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

–odps sql

–author:宋文理
–create time:2023-03-09 09:36:05

– MaxCompute与Hive、Oracle、MySQL的数据类型映射表,如下所示。

MaxCompute数据类型 Hive数据类型 Oracle数据类型 MySQL数据类型
BOOLEAN BOOLEAN 无(说明一) 无(说明二)
TINYINT TINYINT NUMBER(3,0) TINYINT
SMALLINT SMALLINT NUMBER(5,0) SMALLINT
INT INT NUMBER(7,0) MEDIUMINT
INT INT NUMBER(10,0) INT
BIGINT BIGINT NUMBER(20,0) BIGINT
FLOAT FLOAT BINARY_FLOAT FLOAT
DOUBLE DOUBLE BINARY_DOUBLE DOUBLE
DECIMAL DECIMAL NUMBER(P,S) DECIMAL或NUMERIC
STRING STRING VARCHAR或VARCHAR2或CHAR或NCHAR或NVARCHAR3 VARCHAR或CHAR
VARCHAR VARCHAR VARCHAR或VARCHAR2或CHAR或NCHAR或NVARCHAR3 VARCHAR
STRING CHAR CHAR CHAR
BINARY BINARY RAW BINARY或VARBINARY
TIMESTAMP TIMESTAMP TIMESTAMP(N) TIMESTAMP
DATETIME DATE DATE DATETIME
ARRAY ARRAY 不支持 不支持
MAP MAP<key,value> 不支持 不支持
STRUCT STRUCT 不支持 不支持
不支持 UNION 不支持 不支持
说明一 使用过程中用CHAR(1)、INTEGER或NUMBER(1)等进行替代,1为真,0为假。
说明二 使用过程用TINYINT(1)替代。


– 数据类型转换如下
MaxCompute SQL允许数据类型之间的转换,类型转换方式包括显式类型转换和隐式类型转换。

显式类型转换
显式类型转换是通过CAST函数将一种数据类型的值转换为另一种类型的值,在MaxCompute SQL中支持的显式类型转换,如下表所示。 关于CAST的介绍请参见CAST。
From/To BIGINT DOUBLE STRING DATETIME BOOLEAN DECIMAL FLOAT
BIGINT 不涉及 Y Y N Y Y Y
DOUBLE Y 不涉及 Y N Y Y Y
STRING Y Y 不涉及 Y Y Y Y
DATETIME N N Y 不涉及 N N N
BOOLEAN Y Y Y N 不涉及 Y Y
DECIMAL Y Y Y N Y 不涉及 Y
FLOAT Y Y Y N Y Y 不涉及
其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的显式类型转换会失败并报错退出。

示例
SELECT CAST(user_id AS DOUBLE) AS new_id;
SELECT CAST(‘2015-10-01 00:00:00’ AS DATETIME) AS new_date;
SELECT CAST(ARRAY(1,2,3) AS ARRAY);
SELECT CONCAT_WS(‘,’, CAST(ARRAY(1, 2) AS ARRAY));
使用说明和限制
将DOUBLE类型转为BIGINT类型时,小数部分会被截断,例如CAST(1.6 AS BIGINT) = 1。
满足DOUBLE格式的STRING类型转换为BIGINT时,会先将STRING转换为DOUBLE,再将DOUBLE转换为BIGINT,
因此,小数部分会被截断,例如CAST(“1.6” AS BIGINT) = 1。
满足BIGINT格式的STRING类型可以被转换为DOUBLE类型,小数点后保留一位,例如CAST(“1” AS DOUBLE) = 1.0。
日期类型转换时采用默认格式yyyy-mm-dd hh:mi:ss。
部分类型之间不可以通过显式的类型转换,但可以通过SQL内建函数进行转换,例如从BOOLEAN类型转换到STRING类型,可使用函数TO_CHAR,
详情请参见TO_CHAR。而TO_DATE函数同样支持从STRING类型到DATETIME类型的转换,详情请参见TO_DATE。
DECIMAL超出值域,CAST STRING TO DECIMAL可能会出现最高位溢出报错、最低位溢出截断等情况。
DECIMAL类型显示转换为DOUBLE、FLOAT等类型会产生精度损失,对于精度有要求的场景,例如计算金额、费率等,建议使用DECIMAL类型。
MaxCompute支持复杂类型的类型转换功能。其中复杂类型的隐式类型转换要求子类型能够隐式转换,而显示转换要求子类型能够显示转换。
STRUCT类型转换不要求字段名称一致,但是要求字段的数量一致,且对应的字段能够隐式或显示转换。例如:
ARRAY能隐式转换或显示转换为ARRAY。
ARRAY能显示转换为ARRAY,但是不能隐式转换。
ARRAY不能隐式转换或显示转换为ARRAY。
STRUCT<a:BIGINT,b:INT>能隐式转换为STRUCTcol1:STRING,col2:BIGINT,但是不能隐式或显示转换为STRUCT<a:STRING>。
隐式类型转换及其作用域
隐式类型转换是指在运行时,由MaxCompute依据上下文使用环境及类型转换规则自动进行的类型转换。
MaxCompute支持的隐式类型转换规则,如下表所示。
From/To BOOLEAN TINYINT SMALLINT INT BIGINT FLOAT
BOOLEAN Y N N N N N
TINYINT N Y Y Y Y Y
SMALLINT N N Y Y Y Y
INT N N Y Y Y Y
BIGINT N N N N Y Y
FLOAT N N N N Y Y

From/To DOUBLE DECIMAL STRING VARCHAR TIMESTAMP BINARY
DOUBLE Y Y Y Y N N
DECIMAL N Y Y Y N N
STRING Y Y Y Y N N
VARCHAR Y Y N N 不涉及 不涉及
TIMESTAMP N N Y Y Y N
BINARY N N N N N Y
其中,Y表示可以转换,N表示不可以转换,不涉及表示不需要转换。不支持的隐式类型转换会导致异常。
如果在执行时转换失败,也会导致异常。

说明
MaxCompute 2.0新增了DECIMAL类型与DATETIME的常量定义方式,100BD是数值为100的DECIMAL,2017-11-11 00:00:00是DATETIME类型的常量。
VALUES子句和VALUES表中可以直接使用常量定义。
由于隐式类型转换是MaxCompute依据上下文使用环境自动进行的类型转换,因此推荐您在类型不匹配时,显式地用CAST进行转换。
隐式类型转换规则是有发生作用域的。在某些作用域中,只有一部分规则可以生效。
示例
SELECT user_id+age+‘12345’, CONCAT(user_name,user_id,age) FROM user;
不同运算符作用下的隐式转换如下:
关系运算符作用下的隐式转换
关系运算符包括=、<>、<、<=、>、>=、IS NULL、IS NOT NULL、LIKE、RLIKE、IN。由于LIKE、RLIKE、IN的隐式类型转换规则
不同于其他关系运算符,将单独对其进行说明。此处的说明不包含这三种特殊的关系运算符。

当不同类型的数据共同参与关系运算时,按照下述原则进行隐式类型转换。
From/To BIGINT DOUBLE STRING DATETIME BOOLEAN DECIMAL
BIGINT 不涉及 DOUBLE DOUBLE N N DECIMAL
DOUBLE DOUBLE 不涉及 DOUBLE N N DECIMAL
STRING DOUBLE DOUBLE 不涉及 DATETIME N DECIMAL
DATETIME N N DATETIME 不涉及 N N
BOOLEAN N N N N 不涉及 N
DECIMAL DECIMAL DECIMAL DECIMAL N N 不涉及
说明
如果进行比较的两个类型间不能进行隐式类型转换,则该关系运算不能完成,报错退出。
关系运算符的更多详情,请参见运算符。
特殊的关系运算符作用下的隐式转换
特殊的关系运算符包括LIKE、RLIKE、IN。
LIKE和RLIKE的使用方式,如下所示。
source LIKE pattern;
source RLIKE pattern;
说明
LIKE和RLIKE的source和pattern参数均仅接受STRING类型。
其他类型不允许参与运算,也不能进行到STRING类型的隐式类型转换。
IN的使用方式如下。
key IN (value1, value2, …)
说明
IN右侧的VALUE值列表中的数据类型必须一致。
当KEY与VALUES之间比较时,如果数据类型包含BIGINT、DOUBLE、STRING,建议统一转为DOUBLE类型;如
果数据类型包含DATETIME、STRING,建议统一转为DATETIME类型。除此之外不允许其它类型之间的转换。
算术运算符作用下的隐式转换
算术运算符包括+、-、*、/、%,其隐式转换规则如下:
只有STRING、BIGINT、DOUBLE和DECIMAL才能参与算术运算。
STRING在参与运算前会进行隐式类型转换到DOUBLE。
BIGINT和DOUBLE共同参与计算时,会将BIGINT隐式转换为DOUBLE。
日期型和布尔型不允许参与算数运算。
逻辑运算符作用下的隐式转换
逻辑运算符包括and、or、not,其隐式转换规则,如下:
只有BOOLEAN才能参与逻辑运算。
其他类型不允许参与逻辑运算,也不允许其他类型的隐式类型转换。
内建函数涉及到的隐式转换
MaxCompute SQL提供了大量的系统函数,方便您对任意行的一列或多列进行计算,输出任意种的数据类型。其隐式转换规则如下:
在调用函数时,如果输入参数的数据类型与函数定义的参数数据类型不一致,把输入参数的数据类型转换为函数定义的数据类型。
每个MaxCompute SQL内建函数的参数对于允许的隐式类型转换的要求不同,详情请参见内建函数概述。
CASE WHEN作用下的隐式转换
CASE WHEN的详情介绍请参见CASE WHEN表达式。它的隐式转换规则,如下:
如果返回类型只有BIGINT、DOUBLE,统一转换为DOUBLE。
如果返回类型中有STRING类型,统一转换为STRING,如果不能转换(如BOOLEAN类型)则报错。
除此之外不允许其它类型之间的转换。
STRING与DATETIME类型之间的转换
MaxCompute支持STRING类型和DATETIME类型之间的相互转换。转换时使用的格式为yyyy-mm-dd hh:mi:ss。

单位 字符串(忽略大小写) 有效值域
年 yyyy 0001~9999
月 mm 01~12
日 dd 01~28|29|30|31
时 hh 00~23
分 mi 00~59
秒 ss 00~59
说明
各个单位的值域中,如果首位为0,不可省略。例如2014-1-9 12:12:12为非法的DATETIME格式,无法从STRING类型数据转换为DATETIME类型,
必须写为2014-01-09 12:12:12。
只有符合上述格式描述的STRING类型才能够转换为DATETIME类型,例如CAST(“2013-12-31 02:34:34” AS DATETIME),将会
把STRING类型2013-12-31 02:34:34 转换为DATETIME类型。同理,DATETIME转换为STRING时,默认转换为yyyy-mm-dd hh:mi:ss格式。文章来源地址https://www.toymoban.com/news/detail-474140.html

到了这里,关于sql数据类型映射与数据类型转换的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • Elasticsearch的数据类型与映射

    Elasticsearch是一个分布式、实时的搜索和分析引擎,它可以处理大量数据并提供快速、准确的搜索结果。在Elasticsearch中,数据类型和映射是两个重要的概念,它们在存储和查询数据时起着关键的作用。本文将深入探讨Elasticsearch的数据类型与映射,揭示其核心概念、算法原理、

    2024年02月19日
    浏览(35)
  • Redis - 数据类型映射底层结构

    从数据类型上体现就是,同一个数据类型,在不同的情况下会使用不同的编码类型,底层所使用的的数据结构也不相同。 字符串对象的编码可以是 int 、 raw 和 embstr 三者之一。 embstr 编码是专门用于保存简短字符串的一种优化编码方式,与 raw 编码会调用两次内存分配函数分

    2023年04月21日
    浏览(36)
  • 【大数据】Flink SQL 语法篇(一):CREATE

    CREATE 语句用于向当前或指定的 Catalog 中注册库、表、视图或函数。注册后的库、表、视图和函数可以在 SQL 查询中使用。 目前 Flink SQL 支持下列 CREATE 语句: CREATE TABLE CREATE DATABASE CREATE VIEW CREATE FUNCTION 下面的 SQL 语句就是建表语句的定义,根据指定的表名创建一个表,如果同

    2024年02月21日
    浏览(36)
  • Elasticsearch (ES) 搜索引擎: 数据类型、动态映射、多类型(子字段)

    原文链接:https://xiets.blog.csdn.net/article/details/132348634 版权声明:原创文章禁止转载 专栏目录:Elasticsearch 专栏(总目录) ES 映射字段的 数据类型 ,官网文档参考:Field data types。 下面是 ES 常用的一些基本数据类型。 字符串 类型: keyword :类型。 text :文本类型。

    2024年03月23日
    浏览(63)
  • es elasticsearch 八 mapping 映射 、复杂数据类型

    目录 Mapping 映射 复杂数据类型 Mapping 映射 精确匹配 必须和对应字段值安全一致才可查出 全文检索 缩写搜索全程、格式转换 大小写 同义词 全文检索核心原理 分词,初步的倒排索引的建立 重建倒排索引 时态转换、重复数的转换、同义词的转换、大小写的转换 分词器 analy

    2024年02月07日
    浏览(57)
  • spark SQL 怎么将一个时间戳字符串转换成hive支持的时间日期类型?

    在 Spark SQL 中,可以使用 to_timestamp 函数将一个时间戳字符串转换成 Hive 支持的时间日期类型。这个函数的语法如下: 其中,timestampStr 表示要转换的时间戳字符串,format 表示时间戳字符串的格式,格式必须与时间戳字符串的实际格式相匹配。如果不指定格式,Spark 会使用默认

    2024年02月11日
    浏览(46)
  • SQL CREATE INDEX 语句- 提高数据库检索效率的关键步骤

    SQL CREATE INDEX 语句用于在表中创建索引。 索引用于比其他方式更快地从数据库中检索数据。用户无法看到索引,它们只是用于加速搜索/查询。 注意: 使用索引更新表比不使用索引更新表需要更多的时间(因为索引也需要更新)。因此,只在经常进行搜索的列上创建索引。

    2024年02月05日
    浏览(54)
  • 【flink番外篇】19、Datastream数据类型到Table schema映射示例

    一、Flink 专栏 Flink 专栏系统介绍某一知识点,并辅以具体的示例进行说明。 1、Flink 部署系列 本部分介绍Flink的部署、配置相关基础内容。 2、Flink基础系列 本部分介绍Flink 的基础部分,比如术语、架构、编程模型、编程指南、基本的datastream api用法、四大基石等内容。 3、

    2024年01月21日
    浏览(50)
  • ElasticSearch系列 - SpringBoot整合ES:映射中定义字段的数据类型及属性

    ElasticSearch - SpringBoot整合ES:映射定义字段的数据类型及属性 01. ElasticSearch 搜索结果的准确性和召回率是什么? 在Elasticsearch中,搜索结果的准确性和召回率是非常重要的指标,它们反映了搜索引擎的性能和效果。以下是这两个指标的定义和解释: 准确性:搜索结果的准确性

    2024年02月08日
    浏览(46)
  • 【ELK03】ES 索引的Mapping映射详解、数据类型和settings属性设置

    映射(MAPPING)就是es中一个决定了文档如何存储,如何生成索引,字段各种类型定义的过程.类似于我们在关系型数据库中创建一个 表格数据之前先定义表格有哪些字段,每个字段是什么类型 ,然后数据会按照这个配置写入表格,ES中同样是这个过程,它由两种映射组成.一个是 动态映射

    2024年02月03日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包