MariaDB Columnstore引擎使用注意事项
一、字段属性限制
1、varchar最大8000
2、不支持bit类型
3、不支持Reserved keywords保留关键字user、comment、match、key、update、status作为表名、字段名或用户定义的变量、函数或存储过程的名称。
4、不支持zerofill
5、不支持enum枚举类型
6、comment不能携带''引号
create table t1(id int comment '主键''ID')engine=Columnstore;
7、不支持主键自增
二、SQL语句限制
1、查询的字段不在group by里，就不能分组统计
错误写法：
代码语言：sql复制MariaDB [test]> select id from t1 group by name;
ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.MariaDB [test]> select id,name from t1 group by name;
ERROR 1815 (HY000): Internal error: MCS-2021: '`test`.`t1`.`id`' is not in GROUP BY clause. All non-aggregate columns in the SELECT and ORDER BY clause must be included in the GROUP BY clause.正确写法：
代码语言：sql复制MariaDB [test]> select name from t1 group by name; 2、alter不支持多列操作和不支持after
代码语言：sql复制MariaDB [test]> alter table t1 add age tinyint,add address varchar(100);
ERROR 1178 (42000): The storage engine for the table doesn't support Multiple actions in alter table statement is currently not supported by Columnstore.3、字段类型不同 join 关联查询报错，比如表1的id字段为int，表2的字段id为varchar，进行关联查询join就会报错
代码语言：sql复制MariaDB [test]> select t1.id from t1 join t2 on t1.id=t2.cid;
ERROR 1815 (HY000): Internal error: IDB-1002: 't1' and 't2' have incompatible column type specified for join condition.4、alter不支持change/modify更改字段属性
代码语言：sql复制MariaDB [test]> alter table t1 change id id bigint;
ERROR 1815 (HY000): Internal error: CAL0001: Alter table Failed: Changing the datatype of a column is not supported 建表范例
代码语言：sql复制CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) DEFAULT '',
`pad` char(60) NOT NULL DEFAULT ''
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
