SQL类型支持，做了一个测试用例
Closed this issue · 3 comments
mia0x75 commented
建表：
--所有无符号的定点和浮点数不做测试
CREATE TABLE dt_fixed_1(c FIXED(38,0));
CREATE TABLE dt_fixed_2(c FIXED(38,19));
CREATE TABLE dt_fixed_3(c FIXED(38,30));
CREATE TABLE dt_real_1(c REAL(38,0));
CREATE TABLE dt_real_2(c REAL(38,19));
CREATE TABLE dt_real_3(c REAL(38,30));
CREATE TABLE dt_decimal_1(c DECIMAL(38,0));
CREATE TABLE dt_decimal_2(c DECIMAL(38,19));
CREATE TABLE dt_decimal_3(c DECIMAL(38,38));
CREATE TABLE dt_dec_1(c DEC(38,0));
CREATE TABLE dt_dec_2(c DEC(38,19));
CREATE TABLE dt_dec_3(c DEC(38,38));
CREATE TABLE dt_numeric_1(c NUMERIC(38,0));
CREATE TABLE dt_numeric_2(c NUMERIC(38,19));
CREATE TABLE dt_numeric_3(c NUMERIC(38,38));
CREATE TABLE dt_float_1(c FLOAT(38,0));
CREATE TABLE dt_float_2(c FLOAT(38,19));
CREATE TABLE dt_float_3(c FLOAT(38,30));
CREATE TABLE dt_doubld_1(c DOUBLE(38,0));
CREATE TABLE dt_doubld_2(c DOUBLE(38,19));
CREATE TABLE dt_doubld_3(c DOUBLE(38,30));
CREATE TABLE dt_bit (c BIT);
CREATE TABLE dt_bool (c BOOL);
CREATE TABLE dt_boolean (c BOOLEAN);
CREATE TABLE dt_int8 (c TINYINT);
CREATE TABLE dt_uint8 (c TINYINT UNSIGNED);
CREATE TABLE dt_int16 (c SMALLINT);
CREATE TABLE dt_uint16 (c SMALLINT UNSIGNED);
CREATE TABLE dt_int24 (c MEDIUMINT);
CREATE TABLE dt_uint24 (c MEDIUMINT UNSIGNED);
CREATE TABLE dt_int32 (c INT);
CREATE TABLE dt_uint32 (c INT UNSIGNED);
CREATE TABLE dt_int64 (c BIGINT);
CREATE TABLE dt_uint64 (c BIGINT UNSIGNED);
CREATE TABLE dt_binary (c BINARY(16));
CREATE TABLE dt_varbinary (c VARBINARY(16));
CREATE TABLE dt_tinyblob (c TINYBLOB);
CREATE TABLE dt_mediumblob (c MEDIUMBLOB);
CREATE TABLE dt_blob (c BLOB);
CREATE TABLE dt_longblob (c LONGBLOB);
CREATE TABLE dt_tinytext (c TINYTEXT);
CREATE TABLE dt_mediumtext (c MEDIUMTEXT);
CREATE TABLE dt_text (c TEXT);
CREATE TABLE dt_longtext (c LONGTEXT);
CREATE TABLE dt_date (c DATE);
CREATE TABLE dt_time (c TIME);
CREATE TABLE dt_datetime (c DATETIME);
CREATE TABLE dt_timestamp (c TIMESTAMP);
CREATE TABLE dt_year_1 (c YEAR(2)); --5.5后不再支持，不测试
CREATE TABLE dt_year_2 (c YEAR(4));
CREATE TABLE dt_char (c CHAR(32));
CREATE TABLE dt_varchar (c VARCHAR(32));
CREATE TABLE dt_enum (c ENUM('F', 'M'));
CREATE TABLE dt_set (c SET ('Length', 'Height', 'Width', 'Weight', 'Color'));
数据：
insert into dt_date VALUES ('1000-01-01');
insert into dt_date VALUES ('9999-12-31');
insert into dt_date VALUES ('2017-12-06');
insert into dt_time VALUES ('-838:59:59.999999'); --丢失精度
insert into dt_time VALUES ('838:59:59.999999'); --丢失精度
insert into dt_time VALUES ('90:00:00');
insert into dt_time VALUES ('800:00:00');
insert into dt_time VALUES (800);
insert into dt_time VALUES (22);
insert into dt_time VALUES (151413);
insert into dt_time VALUES ('9:6:3');
insert into dt_time VALUES ('12 09');
insert into dt_timestamp VALUES (CURRENT_TIMESTAMP());
insert into dt_timestamp VALUES (CURRENT_TIMESTAMP());
insert into dt_timestamp VALUES (CURRENT_TIMESTAMP());
insert into dt_timestamp VALUES (CURRENT_TIMESTAMP());
insert into dt_timestamp VALUES (CURRENT_TIMESTAMP());
insert into dt_datetime VALUES ('1000-01-01 00:00:00.000000'); --丢失精度
insert into dt_datetime VALUES ('9999-12-31 23:59:59.999999'); --丢失精度
insert into dt_datetime VALUES (NOW());
insert into dt_tinytext VALUES ('');
insert into dt_tinytext VALUES (REPEAT('0', 255)); --超过252就不能处理
insert into dt_tinytext VALUES ('1234567890');
insert into dt_tinytext VALUES ('1234567890~!@#$%^&*()_+":?><`'); --引号没处理
insert into dt_tinytext VALUES ('abcdefghijklmnopqrstuvwxyz');
insert into dt_tinytext VALUES ('ABCDEFGHIJKLMNOPQRSTUVWXYZ');
insert into dt_tinytext VALUES ('你好');
insert into dt_tinytext VALUES ('おはようございます');
insert into dt_tinytext VALUES ('привет');
insert into dt_tinytext VALUES ('namasté');
insert into dt_tinytext VALUES ('여보세요');
insert into dt_text VALUES ('');
insert into dt_text VALUES (REPEAT('0', 65535));--超过252就不能处理
insert into dt_text VALUES (REPEAT('0', 32767));--超过252就不能处理
--应该跟tinytext有相同问题
insert into dt_tinyblob VALUES (0x00010203040506070809);
insert into dt_tinyblob VALUES (0x000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F404142434445464748494A4B4C4D4E4F505152535455565758595A5B5C5D5E5F606162636465666768696A6B6C6D6E6F707172737475767778797A7B7C7D7E7F808182838485868788898A8B8C8D8E8F909192939495969798999A9B9C9D9E9FA0A1A2A3A4A5A6A7A8A9AAABACADAEAFB0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFC0C1C2C3C4C5C6C7C8C9CACBCCCDCECFD0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFE0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFF0F1F2F3F4F5F6F7F8F9FAFBFCFDFE);
insert into dt_bit VALUES (0);
insert into dt_bit VALUES (1);
insert into dt_bool VALUES (0);
insert into dt_bool VALUES (1);
insert into dt_boolean VALUES (0);
insert into dt_boolean VALUES (1);
insert into dt_year_2 VALUES (2000); --一般值
insert into dt_year_2 VALUES (2155); --上边界
insert into dt_year_2 VALUES (0000); --下边界
insert into dt_int8 VALUES (-128); --下边界
insert into dt_int8 VALUES (127); --上边界
insert into dt_int8 VALUES (FLOOR((RAND()-0.5)*255)); --一般值
insert into dt_int8 VALUES (FLOOR((RAND()-0.5)*255)); --一般值
insert into dt_int8 VALUES (FLOOR((RAND()-0.5)*255)); --一般值
insert into dt_int8 VALUES (FLOOR((RAND()-0.5)*255)); --一般值
insert into dt_int8 VALUES (FLOOR((RAND()-0.5)*255)); --一般值
insert into dt_uint8 VALUES (0); --下边界
insert into dt_uint8 VALUES (255); --上边界
insert into dt_uint8 VALUES (FLOOR(RAND()*255)); --一般值
insert into dt_uint8 VALUES (FLOOR(RAND()*255)); --一般值
insert into dt_uint8 VALUES (FLOOR(RAND()*255)); --一般值
insert into dt_uint8 VALUES (FLOOR(RAND()*255)); --一般值
insert into dt_uint8 VALUES (FLOOR(RAND()*255)); --一般值
insert into dt_int16 VALUES (-32768); --下边界
insert into dt_int16 VALUES (32767); --上边界
insert into dt_int16 VALUES (FLOOR((RAND()-0.5)*65535)); --一般值
insert into dt_int16 VALUES (FLOOR((RAND()-0.5)*65535)); --一般值
insert into dt_int16 VALUES (FLOOR((RAND()-0.5)*65535)); --一般值
insert into dt_int16 VALUES (FLOOR((RAND()-0.5)*65535)); --一般值
insert into dt_int16 VALUES (FLOOR((RAND()-0.5)*65535)); --一般值
insert into dt_uint16 VALUES (0); --下边界
insert into dt_uint16 VALUES (65535); --上边界
insert into dt_uint16 VALUES (FLOOR(RAND()*65535)); --一般值
insert into dt_uint16 VALUES (FLOOR(RAND()*65535)); --一般值
insert into dt_uint16 VALUES (FLOOR(RAND()*65535)); --一般值
insert into dt_uint16 VALUES (FLOOR(RAND()*65535)); --一般值
insert into dt_uint16 VALUES (FLOOR(RAND()*65535)); --一般值
insert into dt_int24 VALUES (-8388608); --下边界
insert into dt_int24 VALUES (8388607); --上边界
insert into dt_int24 VALUES (FLOOR((RAND()-0.5)*16777215)); --一般值
insert into dt_int24 VALUES (FLOOR((RAND()-0.5)*16777215)); --一般值
insert into dt_int24 VALUES (FLOOR((RAND()-0.5)*16777215)); --一般值
insert into dt_int24 VALUES (FLOOR((RAND()-0.5)*16777215)); --一般值
insert into dt_int24 VALUES (FLOOR((RAND()-0.5)*16777215)); --一般值
insert into dt_uint24 VALUES (0); --下边界
insert into dt_uint24 VALUES (16777215); --上边界
insert into dt_uint24 VALUES (FLOOR(RAND()*16777215)); --一般值
insert into dt_uint24 VALUES (FLOOR(RAND()*16777215)); --一般值
insert into dt_uint24 VALUES (FLOOR(RAND()*16777215)); --一般值
insert into dt_uint24 VALUES (FLOOR(RAND()*16777215)); --一般值
insert into dt_uint24 VALUES (FLOOR(RAND()*16777215)); --一般值
insert into dt_int32 VALUES (-2147483648); --下边界
insert into dt_int32 VALUES (2147483647); --上边界
insert into dt_int32 VALUES (FLOOR((RAND()-0.5)*4294967295)); --一般值
insert into dt_int32 VALUES (FLOOR((RAND()-0.5)*4294967295)); --一般值
insert into dt_int32 VALUES (FLOOR((RAND()-0.5)*4294967295)); --一般值
insert into dt_int32 VALUES (FLOOR((RAND()-0.5)*4294967295)); --一般值
insert into dt_int32 VALUES (FLOOR((RAND()-0.5)*4294967295)); --一般值
insert into dt_uint32 VALUES (0); --下边界
insert into dt_uint32 VALUES (4294967295); --上边界
insert into dt_uint32 VALUES (FLOOR(RAND()*4294967295)); --一般值
insert into dt_uint32 VALUES (FLOOR(RAND()*4294967295)); --一般值
insert into dt_uint32 VALUES (FLOOR(RAND()*4294967295)); --一般值
insert into dt_uint32 VALUES (FLOOR(RAND()*4294967295)); --一般值
insert into dt_uint32 VALUES (FLOOR(RAND()*4294967295)); --一般值
insert into dt_int64 VALUES (-9223372036854775808); --下边界
insert into dt_int64 VALUES (9223372036854775807); --上边界
insert into dt_int64 VALUES (FLOOR((RAND()-0.5)*18446744073709551615)); --一般值
insert into dt_int64 VALUES (FLOOR((RAND()-0.5)*18446744073709551615)); --一般值
insert into dt_int64 VALUES (FLOOR((RAND()-0.5)*18446744073709551615)); --一般值
insert into dt_int64 VALUES (FLOOR((RAND()-0.5)*18446744073709551615)); --一般值
insert into dt_int64 VALUES (FLOOR((RAND()-0.5)*18446744073709551615)); --一般值
insert into dt_uint64 VALUES (0); --下边界
insert into dt_uint64 VALUES (18446744073709551615); --上边界
insert into dt_uint64 VALUES (FLOOR(RAND()*18446744073709551615)); --一般值
insert into dt_uint64 VALUES (FLOOR(RAND()*18446744073709551615)); --一般值
insert into dt_uint64 VALUES (FLOOR(RAND()*18446744073709551615)); --一般值
insert into dt_uint64 VALUES (FLOOR(RAND()*18446744073709551615)); --一般值
insert into dt_uint64 VALUES (FLOOR(RAND()*18446744073709551615)); --一般值
INSERT INTO dt_doubld_1 VALUES (+99999999999999999999999999999999999999); --上边界
INSERT INTO dt_doubld_1 VALUES (-99999999999999999999999999999999999999); --下边界
INSERT INTO dt_doubld_1 VALUES (+12345678912345678912345678912345678912); --一般值
INSERT INTO dt_doubld_1 VALUES (-12345678912345678912345678912345678912); --一般值
INSERT INTO dt_doubld_2 VALUES (+9999999999999999999.9999999999999999999); --上边界
INSERT INTO dt_doubld_2 VALUES (-9999999999999999999.9999999999999999999); --下边界
INSERT INTO dt_doubld_2 VALUES (+1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_doubld_2 VALUES (-1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_doubld_3 VALUES (+0.99999999999999999999999999999999999999);--上边界
INSERT INTO dt_doubld_3 VALUES (-0.99999999999999999999999999999999999999);--下边界
INSERT INTO dt_doubld_3 VALUES (+0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_doubld_3 VALUES (-0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_float_1 VALUES (+99999999999999999999999999999999999999); --上边界
INSERT INTO dt_float_1 VALUES (-99999999999999999999999999999999999999); --下边界
INSERT INTO dt_float_1 VALUES (+12345678912345678912345678912345678912); --一般值
INSERT INTO dt_float_1 VALUES (-12345678912345678912345678912345678912); --一般值
INSERT INTO dt_float_2 VALUES (+9999999999999999999.9999999999999999999); --上边界
INSERT INTO dt_float_2 VALUES (-9999999999999999999.9999999999999999999); --下边界
INSERT INTO dt_float_2 VALUES (+1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_float_2 VALUES (-1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_float_3 VALUES (+99999999.999999999999999999999999999999); --上边界
INSERT INTO dt_float_3 VALUES (-99999999.999999999999999999999999999999); --下边界
INSERT INTO dt_float_3 VALUES (+12345678.123456789123456789123456789123); --一般值
INSERT INTO dt_float_3 VALUES (-12345678.123456789123456789123456789123); --一般值
INSERT INTO dt_numeric_1 VALUES (+99999999999999999999999999999999999999); --上边界
INSERT INTO dt_numeric_1 VALUES (-99999999999999999999999999999999999999); --下边界
INSERT INTO dt_numeric_1 VALUES (+12345678912345678912345678912345678912); --一般值
INSERT INTO dt_numeric_1 VALUES (-12345678912345678912345678912345678912); --一般值
INSERT INTO dt_numeric_2 VALUES (+9999999999999999999.9999999999999999999); --上边界
INSERT INTO dt_numeric_2 VALUES (-9999999999999999999.9999999999999999999); --下边界
INSERT INTO dt_numeric_2 VALUES (+1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_numeric_2 VALUES (-1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_numeric_3 VALUES (+0.99999999999999999999999999999999999999);--上边界
INSERT INTO dt_numeric_3 VALUES (-0.99999999999999999999999999999999999999);--下边界
INSERT INTO dt_numeric_3 VALUES (+0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_numeric_3 VALUES (-0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_dec_1 VALUES (+99999999999999999999999999999999999999); --上边界
INSERT INTO dt_dec_1 VALUES (-99999999999999999999999999999999999999); --下边界
INSERT INTO dt_dec_1 VALUES (+12345678912345678912345678912345678912); --一般值
INSERT INTO dt_dec_1 VALUES (-12345678912345678912345678912345678912); --一般值
INSERT INTO dt_dec_2 VALUES (+9999999999999999999.9999999999999999999); --上边界
INSERT INTO dt_dec_2 VALUES (-9999999999999999999.9999999999999999999); --下边界
INSERT INTO dt_dec_2 VALUES (+1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_dec_2 VALUES (-1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_dec_3 VALUES (+0.99999999999999999999999999999999999999);--上边界
INSERT INTO dt_dec_3 VALUES (-0.99999999999999999999999999999999999999);--下边界
INSERT INTO dt_dec_3 VALUES (+0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_dec_3 VALUES (-0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_decimal_1 VALUES (+99999999999999999999999999999999999999); --上边界
INSERT INTO dt_decimal_1 VALUES (-99999999999999999999999999999999999999); --下边界
INSERT INTO dt_decimal_1 VALUES (+12345678912345678912345678912345678912); --一般值
INSERT INTO dt_decimal_1 VALUES (-12345678912345678912345678912345678912); --一般值
INSERT INTO dt_decimal_2 VALUES (+9999999999999999999.9999999999999999999); --上边界
INSERT INTO dt_decimal_2 VALUES (-9999999999999999999.9999999999999999999); --下边界
INSERT INTO dt_decimal_2 VALUES (+1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_decimal_2 VALUES (-1234567891234567891.1234567891234567891); --一般值
INSERT INTO dt_decimal_3 VALUES (+0.99999999999999999999999999999999999999);--上边界
INSERT INTO dt_decimal_3 VALUES (-0.99999999999999999999999999999999999999);--下边界
INSERT INTO dt_decimal_3 VALUES (+0.12345678912345678912345678912345678912);--一般值
INSERT INTO dt_decimal_3 VALUES (-0.12345678912345678912345678912345678912);--一般值
应该跟以下类型有关（精度问题）：
dt_decimal
dt_dec
dt_numeric
dt_fixed
dt_float
dt_double
dt_real
目前问题
- datetime 精度问题
- time 精度问题
- 大于252长度问题 text/blob/varchar/varbinary等
- 定点数/浮点数问题
jilieryuyi commented
insert into dt_time VALUES ('-838:59:59.999999');
ERROR 1292 (22007): Incorrect time value: '-838:59:59.999999' for column 'c' at row 1
insert into dt_time VALUES ('838:59:59.999999');
ERROR 1292 (22007): Incorrect time value: '838:59:59.999999' for column 'c' at row 1
另外blod不能写入16进制数据的
insert into dt_tinyblob VALUES (0x00010203040506070809);
其他的都支持了！！
jilieryuyi commented
你的示例没有全走一遍（冷汗表情）
mia0x75 commented
go-mysql问题，包括无符号类型。