mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-05-01 08:44:47 +03:00
64f1d541d0
feat(charset)!: utf8 is a new charset default and utf8_general_ci is a new collation default in the engine configuration file shipped --------- Co-authored-by: Leonid Fedorov <leonid.fedorov@mariadb.com> Co-authored-by: mariadb-DanielLee <daniel.lee@mariadb.com>
1305 lines
51 KiB
Plaintext
1305 lines
51 KiB
Plaintext
drop database if exists test_mcol2000;
|
||
SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',STRICT_TRANS_TABLES');
|
||
create database test_mcol2000;
|
||
use test_mcol2000;
|
||
drop table if exists orig;
|
||
drop table if exists copy1;
|
||
drop table if exists copy2;
|
||
drop table if exists copy3;
|
||
create table orig (a integer not null,
|
||
b int default 0,
|
||
c smallint,
|
||
d tinyint,
|
||
e mediumint,
|
||
f bigint,
|
||
g decimal(3,2),
|
||
h numeric(4,3),
|
||
i float,
|
||
j double,
|
||
l date,
|
||
m datetime,
|
||
o time,
|
||
s char(17) character set utf8,
|
||
t varchar(17) character set utf8mb4,
|
||
w blob(10),
|
||
x tinyblob,
|
||
y blob,
|
||
z mediumblob,
|
||
aa longblob,
|
||
bb text(17) character set utf8,
|
||
cc tinytext character set utf8mb4,
|
||
dd text character set utf8mb4,
|
||
ee mediumtext character set utf8mb4,
|
||
ff longtext character set utf8mb4
|
||
) default charset=koi8r ENGINE=InnoDB;
|
||
create table copy1 like orig;
|
||
alter table copy1 engine=columnstore;
|
||
create table copy2 like copy1;
|
||
create table copy3 engine=columnstore as select * from orig;
|
||
show create table orig;
|
||
Table Create Table
|
||
orig CREATE TABLE `orig` (
|
||
`a` int(11) NOT NULL,
|
||
`b` int(11) DEFAULT 0,
|
||
`c` smallint(6) DEFAULT NULL,
|
||
`d` tinyint(4) DEFAULT NULL,
|
||
`e` mediumint(9) DEFAULT NULL,
|
||
`f` bigint(20) DEFAULT NULL,
|
||
`g` decimal(3,2) DEFAULT NULL,
|
||
`h` decimal(4,3) DEFAULT NULL,
|
||
`i` float DEFAULT NULL,
|
||
`j` double DEFAULT NULL,
|
||
`l` date DEFAULT NULL,
|
||
`m` datetime DEFAULT NULL,
|
||
`o` time DEFAULT NULL,
|
||
`s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`w` tinyblob DEFAULT NULL,
|
||
`x` tinyblob DEFAULT NULL,
|
||
`y` blob DEFAULT NULL,
|
||
`z` mediumblob DEFAULT NULL,
|
||
`aa` longblob DEFAULT NULL,
|
||
`bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`dd` text CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL
|
||
) ENGINE=InnoDB DEFAULT CHARSET=koi8r
|
||
show create table copy1;
|
||
Table Create Table
|
||
copy1 CREATE TABLE `copy1` (
|
||
`a` int(11) NOT NULL,
|
||
`b` int(11) DEFAULT 0,
|
||
`c` smallint(6) DEFAULT NULL,
|
||
`d` tinyint(4) DEFAULT NULL,
|
||
`e` mediumint(9) DEFAULT NULL,
|
||
`f` bigint(20) DEFAULT NULL,
|
||
`g` decimal(3,2) DEFAULT NULL,
|
||
`h` decimal(4,3) DEFAULT NULL,
|
||
`i` float DEFAULT NULL,
|
||
`j` double DEFAULT NULL,
|
||
`l` date DEFAULT NULL,
|
||
`m` datetime DEFAULT NULL,
|
||
`o` time DEFAULT NULL,
|
||
`s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`w` tinyblob DEFAULT NULL,
|
||
`x` tinyblob DEFAULT NULL,
|
||
`y` blob DEFAULT NULL,
|
||
`z` mediumblob DEFAULT NULL,
|
||
`aa` longblob DEFAULT NULL,
|
||
`bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`dd` text CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL
|
||
) ENGINE=Columnstore DEFAULT CHARSET=koi8r
|
||
show create table copy2;
|
||
Table Create Table
|
||
copy2 CREATE TABLE `copy2` (
|
||
`a` int(11) NOT NULL,
|
||
`b` int(11) DEFAULT 0,
|
||
`c` smallint(6) DEFAULT NULL,
|
||
`d` tinyint(4) DEFAULT NULL,
|
||
`e` mediumint(9) DEFAULT NULL,
|
||
`f` bigint(20) DEFAULT NULL,
|
||
`g` decimal(3,2) DEFAULT NULL,
|
||
`h` decimal(4,3) DEFAULT NULL,
|
||
`i` float DEFAULT NULL,
|
||
`j` double DEFAULT NULL,
|
||
`l` date DEFAULT NULL,
|
||
`m` datetime DEFAULT NULL,
|
||
`o` time DEFAULT NULL,
|
||
`s` char(17) CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`w` tinyblob DEFAULT NULL,
|
||
`x` tinyblob DEFAULT NULL,
|
||
`y` blob DEFAULT NULL,
|
||
`z` mediumblob DEFAULT NULL,
|
||
`aa` longblob DEFAULT NULL,
|
||
`bb` tinytext CHARACTER SET utf8mb3 DEFAULT NULL,
|
||
`cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`dd` text CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL
|
||
) ENGINE=Columnstore DEFAULT CHARSET=koi8r
|
||
show create table copy3;
|
||
Table Create Table
|
||
copy3 CREATE TABLE `copy3` (
|
||
`a` int(11) NOT NULL,
|
||
`b` int(11) DEFAULT 0,
|
||
`c` smallint(6) DEFAULT NULL,
|
||
`d` tinyint(4) DEFAULT NULL,
|
||
`e` mediumint(9) DEFAULT NULL,
|
||
`f` bigint(20) DEFAULT NULL,
|
||
`g` decimal(3,2) DEFAULT NULL,
|
||
`h` decimal(4,3) DEFAULT NULL,
|
||
`i` float DEFAULT NULL,
|
||
`j` double DEFAULT NULL,
|
||
`l` date DEFAULT NULL,
|
||
`m` datetime DEFAULT NULL,
|
||
`o` time DEFAULT NULL,
|
||
`s` char(17) DEFAULT NULL,
|
||
`t` varchar(17) CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`w` tinyblob DEFAULT NULL,
|
||
`x` tinyblob DEFAULT NULL,
|
||
`y` blob DEFAULT NULL,
|
||
`z` mediumblob DEFAULT NULL,
|
||
`aa` longblob DEFAULT NULL,
|
||
`bb` tinytext DEFAULT NULL,
|
||
`cc` tinytext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`dd` text CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ee` mediumtext CHARACTER SET utf8mb4 DEFAULT NULL,
|
||
`ff` longtext CHARACTER SET utf8mb4 DEFAULT NULL
|
||
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
|
||
drop table orig;
|
||
drop table copy1;
|
||
drop table copy2;
|
||
drop table copy3;
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
);
|
||
create table i2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
);
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
'1234567890', '2020-10-25 01:02:05',
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('12', '2020-10-25 01:02:05',
|
||
'1234567890', '2020-10-25 01:02:06',
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'a' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
'1234567890', '2020-10-25 01:02:07',
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
insert into cs3 values (_utf8mb4 0xE1B482, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 1234567890 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 1234567890 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
? 2020-10-25 01:02:07 1234567890 2020-10-25 01:02:08 abc 2020-10-25 01:02:09 cde 2020-10-25 01:02:10
|
||
commit;
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a varchar 1 3
|
||
b datetime NULL NULL
|
||
c varchar 10 30
|
||
d datetime NULL NULL
|
||
e varchar 240 720
|
||
f datetime NULL NULL
|
||
g varchar 2000 6000
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a varchar 3
|
||
b datetime 8
|
||
c varchar 30
|
||
d datetime 8
|
||
e varchar 720
|
||
f datetime 8
|
||
g varchar 6000
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a varchar(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a varchar(1) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c varchar(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e varchar(240) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g varchar(2000) CHARACTER SET utf8mb4, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a varchar(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
|
||
);
|
||
create table i2 (a varchar(1) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c varchar(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e varchar(240) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g varchar(2000) CHARACTER SET utf8mb4, h datetime NOT NULL
|
||
);
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a varchar 1 3
|
||
b datetime NULL NULL
|
||
c varchar 10 40
|
||
d datetime NULL NULL
|
||
e varchar 240 720
|
||
f datetime NULL NULL
|
||
g varchar 2000 8000
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a varchar 3
|
||
b datetime 8
|
||
c varchar 40
|
||
d datetime 8
|
||
e varchar 720
|
||
f datetime 8
|
||
g varchar 8000
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table cs2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
) default character set utf8;
|
||
create table i2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
) default character set utf8;
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a varchar 1 3
|
||
b datetime NULL NULL
|
||
c varchar 10 30
|
||
d datetime NULL NULL
|
||
e varchar 240 720
|
||
f datetime NULL NULL
|
||
g varchar 2000 6000
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a varchar 3
|
||
b datetime 8
|
||
c varchar 30
|
||
d datetime 8
|
||
e varchar 720
|
||
f datetime 8
|
||
g varchar 6000
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table cs2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table i1 (a varchar(1) NOT NULL, b datetime NOT NULL,
|
||
c varchar(10) NOT NULL, d datetime NOT NULL,
|
||
e varchar(240) NOT NULL, f datetime NOT NULL,
|
||
g varchar(2000) NOT NULL, h datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
create table i2 (a varchar(1), b datetime NOT NULL,
|
||
c varchar(10), d datetime NOT NULL,
|
||
e varchar(240), f datetime NOT NULL,
|
||
g varchar(2000), h datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
? 2020-10-25 01:02:07 1234567890 2020-10-25 01:02:08 abc 2020-10-25 01:02:09 cde 2020-10-25 01:02:10
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a varchar 1 4
|
||
b datetime NULL NULL
|
||
c varchar 10 40
|
||
d datetime NULL NULL
|
||
e varchar 240 960
|
||
f datetime NULL NULL
|
||
g varchar 2000 8000
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a varchar 4
|
||
b datetime 8
|
||
c varchar 40
|
||
d datetime 8
|
||
e varchar 960
|
||
f datetime 8
|
||
g varchar 8000
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
);
|
||
create table i2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
);
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
'1234567890', '2020-10-25 01:02:05',
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
'12345678901', '2020-10-25 01:02:06',
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
'12345678901', '2020-10-25 01:02:07',
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 1234567890 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 1234567890 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a char 1 3
|
||
b datetime NULL NULL
|
||
c char 10 30
|
||
d datetime NULL NULL
|
||
e char 240 720
|
||
f datetime NULL NULL
|
||
g char 255 765
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a char 3
|
||
b datetime 8
|
||
c char 30
|
||
d datetime 8
|
||
e char 720
|
||
f datetime 8
|
||
g char 765
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a char(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c char(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e char(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g char(255) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a char(1) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c char(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e char(240) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g char(255) CHARACTER SET utf8mb4, h datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a char(1) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c char(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e char(240) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g char(255) CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL
|
||
);
|
||
create table i2 (a char(1) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c char(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e char(240) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g char(255) CHARACTER SET utf8mb4, h datetime NOT NULL
|
||
);
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a char 1 3
|
||
b datetime NULL NULL
|
||
c char 10 40
|
||
d datetime NULL NULL
|
||
e char 240 720
|
||
f datetime NULL NULL
|
||
g char 255 1020
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a char 3
|
||
b datetime 8
|
||
c char 40
|
||
d datetime 8
|
||
e char 720
|
||
f datetime 8
|
||
g char 1020
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table cs2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
) default character set utf8;
|
||
create table i2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
) default character set utf8;
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a char 1 3
|
||
b datetime NULL NULL
|
||
c char 10 30
|
||
d datetime NULL NULL
|
||
e char 240 720
|
||
f datetime NULL NULL
|
||
g char 255 765
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a char 3
|
||
b datetime 8
|
||
c char 30
|
||
d datetime 8
|
||
e char 720
|
||
f datetime 8
|
||
g char 765
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table cs2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table i1 (a char(1) NOT NULL, b datetime NOT NULL,
|
||
c char(10) NOT NULL, d datetime NOT NULL,
|
||
e char(240) NOT NULL, f datetime NOT NULL,
|
||
g char(255) NOT NULL, h datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
create table i2 (a char(1), b datetime NOT NULL,
|
||
c char(10), d datetime NOT NULL,
|
||
e char(240), f datetime NOT NULL,
|
||
g char(255), h datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06'
|
||
);
|
||
insert into i2(b, d, f, h) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06
|
||
select * from cs2;
|
||
a b c d e f g h
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'abc', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:07');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:07',
|
||
'cde', '2020-10-25 01:02:08');
|
||
ERROR 22001: Data too long for column 'c' at row 1
|
||
insert ignore into cs3 values ('12', '2020-10-25 01:02:06',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:07', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'abc', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:09');
|
||
Warnings:
|
||
Warning 1265 Data truncated for column 'a' at row 1
|
||
Warning 1265 Data truncated for column 'c' at row 1
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'abc', '2020-10-25 01:02:09',
|
||
'cde', '2020-10-25 01:02:10');
|
||
select * from cs3;
|
||
a b c d e f g h
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 abc 2020-10-25 01:02:06 cde 2020-10-25 01:02:07
|
||
1 2020-10-25 01:02:06 <09><><EFBFBD>??????? 2020-10-25 01:02:07 abc 2020-10-25 01:02:08 cde 2020-10-25 01:02:09
|
||
? 2020-10-25 01:02:07 1234567890 2020-10-25 01:02:08 abc 2020-10-25 01:02:09 cde 2020-10-25 01:02:10
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a char 1 4
|
||
b datetime NULL NULL
|
||
c char 10 40
|
||
d datetime NULL NULL
|
||
e char 240 960
|
||
f datetime NULL NULL
|
||
g char 255 1020
|
||
h datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a char 4
|
||
b datetime 8
|
||
c char 40
|
||
d datetime 8
|
||
e char 960
|
||
f datetime 8
|
||
g char 1020
|
||
h datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
);
|
||
create table i2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
);
|
||
insert into i1 values ('a', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06',
|
||
'efg', '2020-10-25 01:02:07',
|
||
'fgh', '2020-10-25 01:02:08'
|
||
);
|
||
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06',
|
||
'2020-10-25 01:02:07',
|
||
'2020-10-25 01:02:08'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h i j k l
|
||
a 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06 efg 2020-10-25 01:02:07 fgh 2020-10-25 01:02:08
|
||
select * from cs2;
|
||
a b c d e f g h i j k l
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
'1234567890', '2020-10-25 01:02:05',
|
||
'cde', '2020-10-25 01:02:07',
|
||
'def', '2020-10-25 01:02:08',
|
||
'efg', '2020-10-25 01:02:09',
|
||
'fgh', '2020-10-25 01:02:10');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
'12345678901', '2020-10-25 01:02:06',
|
||
'cde', '2020-10-25 01:02:08',
|
||
'def', '2020-10-25 01:02:09',
|
||
'efg', '2020-10-25 01:02:10',
|
||
'fgh', '2020-10-25 01:02:11');
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:10',
|
||
'def', '2020-10-25 01:02:11',
|
||
'efg', '2020-10-25 01:02:12',
|
||
'fgh', '2020-10-25 01:02:13');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h i j k l
|
||
1 2020-10-25 01:02:04 1234567890 2020-10-25 01:02:05 cde 2020-10-25 01:02:07 def 2020-10-25 01:02:08 efg 2020-10-25 01:02:09 fgh 2020-10-25 01:02:10
|
||
1 2020-10-25 01:02:05 12345678901 2020-10-25 01:02:06 cde 2020-10-25 01:02:08 def 2020-10-25 01:02:09 efg 2020-10-25 01:02:10 fgh 2020-10-25 01:02:11
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a tinytext 255 255
|
||
b datetime NULL NULL
|
||
c tinytext 255 255
|
||
d datetime NULL NULL
|
||
e text 65535 65535
|
||
f datetime NULL NULL
|
||
g text 65535 65535
|
||
h datetime NULL NULL
|
||
i mediumtext 16777215 16777215
|
||
j datetime NULL NULL
|
||
k longtext 4294967295 4294967295
|
||
l datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a text 255
|
||
b datetime 8
|
||
c text 255
|
||
d datetime 8
|
||
e text 65535
|
||
f datetime 8
|
||
g text 65535
|
||
h datetime 8
|
||
i text 16777215
|
||
j datetime 8
|
||
k text 16777215
|
||
l datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a text(8) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c text(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e text(255) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g text CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL,
|
||
i text(65535) CHARACTER SET utf8 NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) CHARACTER SET utf8mb4 NOT NULL, l datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table cs2 (a text(8) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c text(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e text(255) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g text CHARACTER SET utf8mb4, h datetime NOT NULL,
|
||
i text(65535) CHARACTER SET utf8, j datetime NOT NULL,
|
||
k text(16777215) CHARACTER SET utf8mb4, l datetime NOT NULL
|
||
)engine=columnstore;
|
||
create table i1 (a text(8) CHARACTER SET utf8 NOT NULL, b datetime NOT NULL,
|
||
c text(10) CHARACTER SET utf8mb4 NOT NULL, d datetime NOT NULL,
|
||
e text(255) CHARACTER SET utf8 NOT NULL, f datetime NOT NULL,
|
||
g text CHARACTER SET utf8mb4 NOT NULL, h datetime NOT NULL,
|
||
i text(65535) CHARACTER SET utf8 NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) CHARACTER SET utf8mb4 NOT NULL, l datetime NOT NULL
|
||
);
|
||
create table i2 (a text(8) CHARACTER SET utf8, b datetime NOT NULL,
|
||
c text(10) CHARACTER SET utf8mb4, d datetime NOT NULL,
|
||
e text(255) CHARACTER SET utf8, f datetime NOT NULL,
|
||
g text CHARACTER SET utf8mb4, h datetime NOT NULL,
|
||
i text(65535) CHARACTER SET utf8, j datetime NOT NULL,
|
||
k text(16777215) CHARACTER SET utf8mb4, l datetime NOT NULL
|
||
);
|
||
insert into i1 values ('ab', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06',
|
||
'efg', '2020-10-25 01:02:07',
|
||
'fgh', '2020-10-25 01:02:08'
|
||
);
|
||
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06',
|
||
'2020-10-25 01:02:07',
|
||
'2020-10-25 01:02:08'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h i j k l
|
||
ab 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06 efg 2020-10-25 01:02:07 fgh 2020-10-25 01:02:08
|
||
select * from cs2;
|
||
a b c d e f g h i j k l
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'cde', '2020-10-25 01:02:07',
|
||
'def', '2020-10-25 01:02:08',
|
||
'efg', '2020-10-25 01:02:09',
|
||
'fgh', '2020-10-25 01:02:10');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'cde', '2020-10-25 01:02:08',
|
||
'def', '2020-10-25 01:02:09',
|
||
'efg', '2020-10-25 01:02:10',
|
||
'fgh', '2020-10-25 01:02:11');
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:10',
|
||
'def', '2020-10-25 01:02:11',
|
||
'efg', '2020-10-25 01:02:12',
|
||
'fgh', '2020-10-25 01:02:13');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h i j k l
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 cde 2020-10-25 01:02:07 def 2020-10-25 01:02:08 efg 2020-10-25 01:02:09 fgh 2020-10-25 01:02:10
|
||
1 2020-10-25 01:02:05 <09><><EFBFBD>???????<3F> 2020-10-25 01:02:06 cde 2020-10-25 01:02:08 def 2020-10-25 01:02:09 efg 2020-10-25 01:02:10 fgh 2020-10-25 01:02:11
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a tinytext 255 255
|
||
b datetime NULL NULL
|
||
c tinytext 255 255
|
||
d datetime NULL NULL
|
||
e text 65535 65535
|
||
f datetime NULL NULL
|
||
g text 65535 65535
|
||
h datetime NULL NULL
|
||
i mediumtext 16777215 16777215
|
||
j datetime NULL NULL
|
||
k longtext 4294967295 4294967295
|
||
l datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a text 255
|
||
b datetime 8
|
||
c text 255
|
||
d datetime 8
|
||
e text 65535
|
||
f datetime 8
|
||
g text 65535
|
||
h datetime 8
|
||
i text 16777215
|
||
j datetime 8
|
||
k text 16777215
|
||
l datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table cs2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
)engine=columnstore default character set utf8;
|
||
create table i1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
) default character set utf8;
|
||
create table i2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
) default character set utf8;
|
||
insert into i1 values ('ab', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06',
|
||
'efg', '2020-10-25 01:02:07',
|
||
'fgh', '2020-10-25 01:02:08'
|
||
);
|
||
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06',
|
||
'2020-10-25 01:02:07',
|
||
'2020-10-25 01:02:08'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h i j k l
|
||
ab 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06 efg 2020-10-25 01:02:07 fgh 2020-10-25 01:02:08
|
||
select * from cs2;
|
||
a b c d e f g h i j k l
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'cde', '2020-10-25 01:02:07',
|
||
'def', '2020-10-25 01:02:08',
|
||
'efg', '2020-10-25 01:02:09',
|
||
'fgh', '2020-10-25 01:02:10');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'cde', '2020-10-25 01:02:08',
|
||
'def', '2020-10-25 01:02:09',
|
||
'efg', '2020-10-25 01:02:10',
|
||
'fgh', '2020-10-25 01:02:11');
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:10',
|
||
'def', '2020-10-25 01:02:11',
|
||
'efg', '2020-10-25 01:02:12',
|
||
'fgh', '2020-10-25 01:02:13');
|
||
ERROR 22007: Incorrect string value: '\xF0\x9F\x98\x8E' for column `test_mcol2000`.`cs3`.`a` at row 1
|
||
select * from cs3;
|
||
a b c d e f g h i j k l
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 cde 2020-10-25 01:02:07 def 2020-10-25 01:02:08 efg 2020-10-25 01:02:09 fgh 2020-10-25 01:02:10
|
||
1 2020-10-25 01:02:05 <09><><EFBFBD>???????<3F> 2020-10-25 01:02:06 cde 2020-10-25 01:02:08 def 2020-10-25 01:02:09 efg 2020-10-25 01:02:10 fgh 2020-10-25 01:02:11
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a tinytext 255 255
|
||
b datetime NULL NULL
|
||
c tinytext 255 255
|
||
d datetime NULL NULL
|
||
e text 65535 65535
|
||
f datetime NULL NULL
|
||
g text 65535 65535
|
||
h datetime NULL NULL
|
||
i mediumtext 16777215 16777215
|
||
j datetime NULL NULL
|
||
k longtext 4294967295 4294967295
|
||
l datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a text 255
|
||
b datetime 8
|
||
c text 255
|
||
d datetime 8
|
||
e text 65535
|
||
f datetime 8
|
||
g text 65535
|
||
h datetime 8
|
||
i text 16777215
|
||
j datetime 8
|
||
k text 16777215
|
||
l datetime 8
|
||
drop table if exists cs1;
|
||
drop table if exists cs2;
|
||
drop table if exists cs3;
|
||
drop table if exists i1;
|
||
drop table if exists i2;
|
||
create table cs1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table cs2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
)engine=columnstore default character set utf8mb4;
|
||
create table i1 (a text(8) NOT NULL, b datetime NOT NULL,
|
||
c text(10) NOT NULL, d datetime NOT NULL,
|
||
e text(255) NOT NULL, f datetime NOT NULL,
|
||
g text NOT NULL, h datetime NOT NULL,
|
||
i text(65535) NOT NULL, j datetime NOT NULL,
|
||
k text(16777215) NOT NULL, l datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
create table i2 (a text(8), b datetime NOT NULL,
|
||
c text(10), d datetime NOT NULL,
|
||
e text(255), f datetime NOT NULL,
|
||
g text, h datetime NOT NULL,
|
||
i text(65535), j datetime NOT NULL,
|
||
k text(16777215), l datetime NOT NULL
|
||
) default character set utf8mb4;
|
||
insert into i1 values ('ab', '2020-10-25 01:02:03',
|
||
'bcd', '2020-10-25 01:02:04',
|
||
'cde', '2020-10-25 01:02:05',
|
||
'def', '2020-10-25 01:02:06',
|
||
'efg', '2020-10-25 01:02:07',
|
||
'fgh', '2020-10-25 01:02:08'
|
||
);
|
||
insert into i2(b, d, f, h, j, l) values ('2020-10-25 01:02:03',
|
||
'2020-10-25 01:02:04',
|
||
'2020-10-25 01:02:05',
|
||
'2020-10-25 01:02:06',
|
||
'2020-10-25 01:02:07',
|
||
'2020-10-25 01:02:08'
|
||
);
|
||
insert into cs1 select * from i1;
|
||
insert into cs2 select * from i2;
|
||
select * from cs1;
|
||
a b c d e f g h i j k l
|
||
ab 2020-10-25 01:02:03 bcd 2020-10-25 01:02:04 cde 2020-10-25 01:02:05 def 2020-10-25 01:02:06 efg 2020-10-25 01:02:07 fgh 2020-10-25 01:02:08
|
||
select * from cs2;
|
||
a b c d e f g h i j k l
|
||
NULL 2020-10-25 01:02:03 NULL 2020-10-25 01:02:04 NULL 2020-10-25 01:02:05 NULL 2020-10-25 01:02:06 NULL 2020-10-25 01:02:07 NULL 2020-10-25 01:02:08
|
||
create table cs3 like cs1;
|
||
insert into cs3 values ('1', '2020-10-25 01:02:04',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0, '2020-10-25 01:02:05', -- ¹²³⁴⁵⁶⁷⁸⁹⁰
|
||
'cde', '2020-10-25 01:02:07',
|
||
'def', '2020-10-25 01:02:08',
|
||
'efg', '2020-10-25 01:02:09',
|
||
'fgh', '2020-10-25 01:02:10');
|
||
insert into cs3 values ('1', '2020-10-25 01:02:05',
|
||
_utf8mb4 0xc2b9c2b2c2b3e281b4e281b5e281b6e281b7e281b8e281b9e281b0c2b9, '2020-10-25 01:02:06', -- ¹²³⁴⁵⁶⁷⁸⁹⁰¹
|
||
'cde', '2020-10-25 01:02:08',
|
||
'def', '2020-10-25 01:02:09',
|
||
'efg', '2020-10-25 01:02:10',
|
||
'fgh', '2020-10-25 01:02:11');
|
||
insert into cs3 values (_utf8mb4 0xF09F988E, '2020-10-25 01:02:07',
|
||
'1234567890', '2020-10-25 01:02:08',
|
||
'cde', '2020-10-25 01:02:10',
|
||
'def', '2020-10-25 01:02:11',
|
||
'efg', '2020-10-25 01:02:12',
|
||
'fgh', '2020-10-25 01:02:13');
|
||
select * from cs3;
|
||
a b c d e f g h i j k l
|
||
1 2020-10-25 01:02:04 <09><><EFBFBD>??????? 2020-10-25 01:02:05 cde 2020-10-25 01:02:07 def 2020-10-25 01:02:08 efg 2020-10-25 01:02:09 fgh 2020-10-25 01:02:10
|
||
1 2020-10-25 01:02:05 <09><><EFBFBD>???????<3F> 2020-10-25 01:02:06 cde 2020-10-25 01:02:08 def 2020-10-25 01:02:09 efg 2020-10-25 01:02:10 fgh 2020-10-25 01:02:11
|
||
? 2020-10-25 01:02:07 1234567890 2020-10-25 01:02:08 cde 2020-10-25 01:02:10 def 2020-10-25 01:02:11 efg 2020-10-25 01:02:12 fgh 2020-10-25 01:02:13
|
||
select column_name, data_type, character_maximum_length, character_octet_length from information_schema.columns where table_schema='test_mcol2000' and table_name='cs3';
|
||
column_name data_type character_maximum_length character_octet_length
|
||
a tinytext 255 255
|
||
b datetime NULL NULL
|
||
c tinytext 255 255
|
||
d datetime NULL NULL
|
||
e text 65535 65535
|
||
f datetime NULL NULL
|
||
g text 65535 65535
|
||
h datetime NULL NULL
|
||
i mediumtext 16777215 16777215
|
||
j datetime NULL NULL
|
||
k longtext 4294967295 4294967295
|
||
l datetime NULL NULL
|
||
select column_name, data_type, column_length from information_schema.columnstore_columns where hex(table_schema)=hex('test_mcol2000') and hex(table_name)=hex('cs3');
|
||
column_name data_type column_length
|
||
a text 255
|
||
b datetime 8
|
||
c text 255
|
||
d datetime 8
|
||
e text 65535
|
||
f datetime 8
|
||
g text 65535
|
||
h datetime 8
|
||
i text 16777215
|
||
j datetime 8
|
||
k text 16777215
|
||
l datetime 8
|
||
drop table cs1;
|
||
drop table cs2;
|
||
drop table cs3;
|
||
drop table i1;
|
||
drop table i2;
|
||
drop database test_mcol2000;
|