[大数据]MySql、PostGreSql中关于Char和VarChar的设定

在修复相关缺陷时发现不同的数据库有不同的语义，因此整理不同数据库的关系如下。

MySql

create table

create table table_char_test
(
charColumn    char(3)    null,
varCharColumn varchar(3) null
);

insert into calcite.table_char_test values ('a','a');
insert into calcite.table_char_test values ('ab','ab');
insert into calcite.table_char_test values ('ab ','ab ');
insert into calcite.table_char_test values ('abc','abc');
# Data truncation: Data too long for column 'varCharColumn' at row 1
insert into calcite.table_char_test values ('abc','abcd');
# Data truncation: Data too long for column 'charColumn' at row 1
insert into calcite.table_char_test values ('abcd','abc');

#UNSET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
select * from calcite.table_char_test where charColumn in ('ab '); # return null
#SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';(PADDING CHAR TO FULL LENGHT)
select * from calcite.table_char_test where charColumn in ('ab'); # return null # in list 不做转化 用户保证
select * from calcite.table_char_test where charColumn in ('ab '); # retur 1 rows
select * from calcite.table_char_test where charColumn in ('ab  '); # return null
# varChar == string
select * from calcite.table_char_test where varCharColumn in ('ab ','abc');# return 2 rows

结论

在MySql中，默认没有设置PAD_CHAR_TO_FULL_LENGTH属性，则Char只在存储时占指定字符空间。其在查询时候都按照插入时候truncate掉尾部多余的值进行比较，in列表中的字符串长度不变，转为bpchar[]，如果是单个值则为bp。
如果设置PAD_CHAR_TO_FULL_LENGTH，则将列值添加空字符串直到规定长度，然后直接和列值做比较。

PostGreSQL

create table

create table table_char_test
(
charColumn    character (3)    null,
varCharColumn character  varying(3) null
);

insert into calcite.table_char_test values ('a','a');
insert into calcite.table_char_test values ('ab','ab');
insert into calcite.table_char_test values ('ab ','ab ');
insert into calcite.table_char_test values ('abc','abc');
# ERROR:  value too long for type character varying(3)
insert into calcite.table_char_test values ('abc','abcd');
# ERROR:  value too long for type character(3)
insert into calcite.table_char_test values ('abcd','abc');

select * from table_char_test where charColumn in ('ab'); # return 2 rows 填充ab的长度
select * from calcite.table_char_test where charColumn in ('ab '); # retur 1 rows
select * from calcite.table_char_test where charColumn in ('ab  '); # return 1 rows
select * from table_char_test where charColumn in ('ab   ','abc   ') # return 3 rows
select * from table_char_test where charColumn in ('ab  b','abc   ') # return 1 row 是去扩充charColumn的长度，而不是去cast list
# character varying == string
select * from calcite.table_char_test where varCharColumn in ('ab ','abc'); # return 2 rows

结论：
charColumn 与 list中谁少谁进行填充，都填充到最大的那个值。