#数据类型转换

数据类型转换多发生在操作的输入类型与规定类型不一致，或一个表达式中包含多个不同数据类型的情况中，此时，可以通过隐式转换或显式转换来整合数据类型。

# 隐式转换

下表中的列标题表示原始类型，行标题表示目标类型，✓表示支持转换，X表示不支持转换，--表示无需转换。

(1)

数据类型	TINYINT	SMALLINT	INTEGER	BIGINT	TINYINT UNSIGNED	SMALLINT UNSIGNED	INTEGER UNSIGNED	BIGINT UNSIGNED
TINYINT	--	✓	✓	✓	✓	✓	✓	✓
SMALLINT	✓	--	✓	✓	✓	✓	✓	✓
INTEGER	✓	✓	--	✓	✓	✓	✓	✓
BIGINT	✓	✓	✓	--	✓	✓	✓	✓
TINYINT UNSIGNED	✓	✓	✓	✓	--	✓	✓	✓
SMALLINT UNSIGNED	✓	✓	✓	✓	✓	--	✓	✓
INTEGER UNSIGNED	✓	✓	✓	✓	✓	✓	--	✓
BIGINT UNSIGNED	✓	✓	✓	✓	✓	✓	✓	--
FLOAT	✓	✓	✓	✓	✓	✓	✓	✓
DOUBLE	✓	✓	✓	✓	✓	✓	✓	✓
DECIMAL	✓	✓	✓	✓	✓	✓	✓	✓
CHAR(SIZE)	✓	✓	✓	✓	✓	✓	✓	✓
VARCHAR(SIZE)	✓	✓	✓	✓	✓	✓	✓	✓
DATE	✓	✓	✓	✓	✓	✓	✓	✓
TIME	✓	✓	✓	✓	✓	✓	✓	✓
TIMESTAMP	✓	✓	✓	✓	✓	✓	✓	✓
BINARY(SIZE) VARBINARY(SIZE)	✓	✓	✓	✓	✓	✓	✓	✓
TINYTEXT MEDIUMTEXT TEXT LONGTEXT	✓	✓	✓	✓	X	X	X	X
TINYBLOB MEDIUMBLOB BLOB LONGBLOB	✓	✓	✓	✓	X	X	X	X

(2)

数据类型	FLOAT	DOUBLE	DECIMAL	CHAR(SIZE)	VARCHAR(SIZE)	DATE	TIME*	TIMESTAMP
TINYINT	✓	✓	✓	✓	✓	✓	✓	✓
SMALLINT	✓	✓	✓	✓	✓	✓	✓	✓
INTEGER	✓	✓	✓	✓	✓	✓	✓	✓
BIGINT	✓	✓	✓	✓	✓	✓	✓	✓
TINYINT UNSIGNED	✓	✓	✓	✓	✓	✓	✓	✓
SMALLINT UNSIGNED	✓	✓	✓	✓	✓	✓	✓	✓
INTEGER UNSIGNED	✓	✓	✓	✓	✓	✓	✓	✓
BIGINT UNSIGNED	✓	✓	✓	✓	✓	✓	✓	✓
FLOAT	--	✓	✓	✓	✓	✓	✓	✓
DOUBLE	✓	--	✓	✓	✓	✓	✓	✓
DECIMAL	✓	✓	--	✓	✓	✓	✓	✓
CHAR(SIZE)	✓	✓	✓	--	✓	✓	✓	✓
VARCHAR(SIZE)	✓	✓	✓	✓	--	✓	✓	✓
DATE	✓	✓	✓	✓	✓	--	✓	✓
TIME	✓	✓	✓	✓	✓	✓	--	✓
TIMESTAMP	✓	✓	✓	✓	✓	✓	✓	--
BINARY(SIZE) VARBINARY(SIZE)	✓	✓	✓	✓	✓	✓	✓	✓
TINYTEXT MEDIUMTEXT TEXT LONGTEXT	X	X	✓	✓	✓	✓	✓	✓
TINYBLOB MEDIUMBLOB BLOB LONGBLOB	X	X	✓	✓	✓	✓	✓	✓

(3)

数据类型	BINARY(SIZE)	VARBINARY(SIZE)	TINYTEXT MEDIUMTEXT TEXT LONGTEXT	TINYBLOB MEDIUMBLOB BLOB LONGBLOB
TINYINT	✓	✓	✓	X
SMALLINT	✓	✓	✓	X
INTEGER	✓	✓	✓	X
BIGINT	✓	✓	✓	X
TINYINT UNSIGNED	✓	✓	X	X
SMALLINT UNSIGNED	✓	✓	X	X
INTEGER UNSIGNED	✓	✓	X	X
BIGINT UNSIGNED	✓	✓	X	X
FLOAT	✓	✓	✓	X
DOUBLE	✓	✓	✓	X
DECIMAL	✓	✓	✓	X
CHAR(SIZE)	✓	✓	✓	✓
VARCHAR(SIZE)	✓	✓	✓	✓
DATE	✓	✓	✓	✓
TIME	✓	✓	X	X
TIMESTAMP	✓	✓	X	X
BINARY(SIZE) VARBINARY(SIZE)	--	✓	✓	✓
TINYTEXT MEDIUMTEXT TEXT LONGTEXT	✓	✓	--	✓
TINYBLOB MEDIUMBLOB BLOB LONGBLOB	✓	✓	✓	--

显式转换是通过类型转换函数来清楚直言地指定转换的方向。相比隐式转换，显式转换可以使SQL语句更加容易理解，输出类型的可预测性更强。

下表举例列示YashanDB在mysql模式下支持的MySQL类型转换函数：

函数	功能
BIN	其他类型向BIT类型转换（返回结果以VARCHAR类型承载该BIT值）。
CAST	向指定类型转换。
CONVERT	向指定类型或字符集转换。

Note:

CHAR和VARCHAR类型的数据列为空字符串时，不允许与数值类型的数据列进行比较或转换。

当将字符串格式的浮点数通过隐式转换或显式转换为整数类型时，将按照四舍五入进行转换处理。