mysql索引创建及达梦数据库添加大字段问题介绍

关于近段时间内在工作中遇到的关于数据库的问题探讨。
一、 mysql创建索引问题分析：
MYSQL数据库创建索引时长度限制导致索引创建失败，在问题排查解决过程中总共遇到两种情况下的错误提示，具体如下：
当创建索引时出现错误提示信息“Specified key was too long; max key length is 1000 bytes”和“Specified key was too long; max key length is 767 bytes”两种情况。
首先分析上述两个错误提示信息属于同一类错误，区别在于造成上述两种情况的原因是指定的存储引擎的不同导致的 ：ENGINE=MYISAM 和 ENGINE=InnoDB；
1、InnoDB存储引擎：
多索引每个列的长度不能大于767 bytes；
所有组成索引列的长度和不能大于3072 bytes。
情况分析：
1）mySql数据库版本是5.6及以下，默认参数配置innodb_large_prefix=off，如果创建索引字段的字段长度大于767字节，就会提示“Specified key was too long; max key length is 767 bytes”；


解决办法：set global innodb_large_prefix=on;并且创建表时指定ROW_FORMAT=DYNAMIC或者使用ALTER TABLE table_name ROW_FORMAT=DYNAMIC。
按照上述解决办法处理之后，多列索引的长度可以大于767字节，但是要注意所有列的索引总长度还是有限制的，不能超过3072字节，超过此长度即提示“Specified key was too long; max key length is 3072 bytes”。


2）MYSQL5.6版本以上,mysql的参数默认innodb_large_prefix=on、innodb_default_row_format=dynamic,默认允许创建长字段的索引，但是最大限制还是3072字节。

2 、MYISAM存储引擎：
多列索引长度限制每个列的长度不能大于1000 bytes，所有组成索引列的长度和不能大于1000 bytes；该情况不受参数innodb_large_prefix值的影响，及不论该参数是开启（on）还是关闭（off），该引擎下的索引最大长度限制都是1000字节。

二、mysql中的varchar介绍：
上述索引创建时，字段的数据类型为varchar，关于mysql中的varchar存储规则介绍：
mysql4.0版本以下，varchar(20)，指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；
mysql5.0版本以上，varchar(20)，指的是20字符，无论存放的是数字、字母还是UTF8汉字（每个UTF8汉字3字节），都可以存放20个。
varchar最大存储65535个字节，但是受字符集限制，在mysql5.0以后varchar的最大长度是不一样的：
字符集为gbk，每个字符最多占2个字节，最大长度不能超过32766;
字符集为utf8，每个字符最多占3个字节，最大长度不能超过21845；
字符集为utf8mb4，每个字符最多占4个字节，最大长度不能超过16383；
…

所以上述创建索引提示的字段最大限制767,3072和1000对应的varchar实际长度分别是255、1024和333(以字符集为UTF-8为标准)。
参考：https://cloud.tencent.com/developer/article/1005696
三、达梦数据库新增一个数据类型为text的字段问题
达梦数据库新增一个数据类型为text的字段时，报错提示“…不能同时不包含聚合KEY和大字段”。

alter table table_name add column (cloumn TEXT)

出现上述错误提示的原因是在创建表的时候没有指定“NOT CLUSTER”，直接就是唯一约束“PRIMARY KEY”或者是“CLUSTER PRIMARY KEY”，所以会出现上述错误提示。
**解决办法：**在创建表的时候直接指定“NOT CLUSTER PRIMARY KEY”，这样就可以随时添加大字段了。
但是，如果是通过DM管理工具创建表或者使用系统框架(例如：hibernate)建表，主键不会指定“CLUSTER” 或者 “NOT CLUSTER”，所以此时需要考虑修改数据库的配置文件dm.ini，需要修改的参数是PK_WITH_CLUSTER = 0。
参考：https://www.cnblogs.com/nihaorz/p/11804891.html