字段屬性限制
1、不支持CHARACTER SET語(yǔ)法
MariaDB [test]> create table t1(id int,name varchar(10) CHARACTER SET utf8)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

成都創(chuàng)新互聯(lián)作為成都網(wǎng)站建設(shè)公司，專注成都網(wǎng)站建設(shè)公司、網(wǎng)站設(shè)計(jì)，有關(guān)企業(yè)網(wǎng)站建設(shè)方案、改版、費(fèi)用等問題，行業(yè)涉及成都社區(qū)文化墻等多個(gè)領(lǐng)域，已為上千家企業(yè)服務(wù)，得到了客戶的尊重與認(rèn)可。

2、不支持COLLATE語(yǔ)法
MariaDB [test]> create table t1(id int)
-> engine=Columnstore COLLATE=utf8_bin;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(id int,name varchar(10) COLLATE utf8_bin)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

3、不支持TEXT/BLOB
MariaDB [test]> create table t1(id int,info text)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.
MariaDB [test]>

MariaDB [test]> create table t1(id int,info blob)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

4、不支持timestamp，后面的DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP全部刪除
MariaDB [test]> create table t1(update_time timestamp)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
-> engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

5、decimal不能大于18
MariaDB [test]> create table t1(money decimal(19,2)) engine=Columnstore;
ERROR 1815 (HY000): Internal error: CAL0009: (3)Create table failed due to  Syntax error: The maximum precision (total number of digits) that can be specified is 18

6、不支持ROW_FORMAT=COMPACT
MariaDB [test]> create table t1(id int)engine=Columnstore ROW_FORMAT=COMPACT;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

7、varchar最大8000
MariaDB [test]> create table t1(name varchar(8001))engine=Columnstore;
ERROR 1815 (HY000): Internal error: CAL0009: (3)Create table failed due to char, varchar and varbinary length may not exceed 8000

8、不支持bit類型
MariaDB [test]> create table t1(status bit)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

9、comment不能攜帶''引號(hào)
MariaDB [test]> create table t3(id int comment '主鍵''ID')engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

10、行溢出，一行varchar不能大于65535（UTF8要除以3）
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

11、不支持enum枚舉類型
MariaDB [test]> create table t1(`type` enum('HOLIDAY','WORKDAY') DEFAULT NULL)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

12、不支持zerofill
MariaDB [test]> create table t2(id int(6) zerofill)engine=columnstore;       
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

BUG
不支持Reserved keywords保留關(guān)鍵字user、comment、match、key、update、status
https://jira.mariadb.org/browse/MCOL-1022
MariaDB [test]> create table user(id int)engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

MariaDB [test]> create table t1(comment varchar(100))engine=Columnstore;
ERROR 1178 (42000): The storage engine for the table doesn’t support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

SQL語(yǔ)句限制

1、distinct的字段不在order by里，就不能排序

MariaDB [test]> select distinct apply_time from test ORDER BY id limit 1;          
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-2022: ORDER BY column not in DISTINCT list.

https://jira.mariadb.org/browse/MCOL-1036

2、查詢的字段不在group by里，就不能分組統(tǒng)計(jì)

MariaDB [test]> select id from test group by qq;  
ERROR 1815 (HY000): Internal error: IDB-2021: 'test.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.

3、alter不支持change/modify更改字段屬性

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

4、alter不支持多列操作和不支持after

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.

MariaDB [test]> alter table t1 add age tinyint after id;  
ERROR 1178 (42000): The storage engine for the table doesn't support The syntax or the data type(s) is not supported by Columnstore. Please check the Columnstore syntax guide for supported syntax or data types.

5、字段類型不同 join 關(guān)聯(lián)查詢報(bào)錯(cuò)

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.

6、DML語(yǔ)句會(huì)非常慢
DML, i.e. INSERT, UPDATE, and DELETE, provide row level changes. ColumnStore is optimized towards bulk modifications and so these operations are slower than they would be in say InnoDB.

• Currently ColumnStore does not support operating as a replication slave target.

https://mariadb.com/kb/en/library/columnstore-data-ingestion/

7、大批量數(shù)據(jù)導(dǎo)入text字段報(bào)內(nèi)存不足

Internal error: CAL0001: Insert Failed:  IDB-2008: The version buffer overflowed.

https://jira.mariadb.org/browse/MCOL-1056

8、不支持replace into和load data infile replace into

https://jira.mariadb.org/browse/MCOL-1080

MariaDB官方回復(fù)：覆蓋寫目前優(yōu)先級(jí)較低，因?yàn)檫@是一個(gè)非常大的項(xiàng)目，預(yù)計(jì)執(zhí)行成本很高。

網(wǎng)站標(biāo)題：MariaDBColumnStore一些限制和BUG總結(jié)
文章來源：http://jinyejixie.com/article0/jjhdio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、網(wǎng)站排名、服務(wù)器托管、軟件開發(fā)、手機(jī)網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)