mysql Specified key was too long; max key length is 767 bytes

mysql建表错误

   cursor.execute(statement, parameters)
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/cursors.py", line 247, in execute
    res = self._query(query)
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/cursors.py", line 374, in _do_query
    db.query(q)
  File "/home/wifi/supersetpy35/lib/python3.5/site-packages/MySQLdb/connections.py", line 277, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1071, 'Specified key was too long; max key length is 767 bytes') [SQL: '\nCREATE TABLE clusters (\n\tcreated_on DATETIME NOT NULL, \n\tchanged_on DATETIME NOT NULL, \n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcluster_name VARCHAR(250), \n\tcoordinator_host VARCHAR(255), \n\tcoordinator_port INTEGER, \n\tcoordinator_endpoint VARCHAR(255), \n\tbroker_host VARCHAR(255), \n\tbroker_port INTEGER, \n\tbroker_endpoint VARCHAR(255), \n\tmetadata_last_refreshed DATETIME, \n\tcreated_by_fk INTEGER, \n\tchanged_by_fk INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (cluster_name), \n\tFOREIGN KEY(created_by_fk) REFERENCES ab_user (id), \n\tFOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)\n)\n\n']

查看数据库的的字符集：

MySQL [superset]> show variables like "%char%";
+--------------------------+-------------------------------------------------------+
| Variable_name            | Value                                                 |
+--------------------------+-------------------------------------------------------+
| character_set_client     | utf8                                                  |
| character_set_connection | utf8                                                  |
| character_set_database   | utf8mb4                                               |
| character_set_filesystem | binary                                                |
| character_set_results    | utf8                                                  |
| character_set_server     | utf8                                                  |
| character_set_system     | utf8                                                  |
| character_sets_dir       | /opt/udb/program/mysql/percona-5.6.21/share/charsets/ |
+--------------------------+-------------------------------------------------------+

发现数据库的字符集是 utf8mb4

这问题一看就是Mysql的字段设置的太长了，于是我把对应字段的长度改了一下就好了。

mysql建立索引时，数据库计算key的长度是累加所有Index用到的字段的char长度后再按下面比例乘起来不能超过限定的key长度1000：
latin1 = 1 byte = 1 character
uft8 = 3 byte = 1 character
gbk = 2 byte = 1 character
举例能看得更明白些，以GBK为例：
CREATE UNIQUE INDEX unique_record ON reports (report_name, report_client, report_city);
其中report_name varchar(200), report_client varchar(200), report_city varchar(200)
(200 + 200 +200) * 2 = 1200 > 1000，所有就会报1071错误，只要将report_city改为varchar(100)那么索引就能成功建立。

这问题主要和mysql的版本有关

不过我的问题解决办法是

MySQL [superset]> alter database superset character set utf8;                      
Query OK, 1 row affected (0.00 sec)

MySQL [superset]> show variables like "%char%";                            
+--------------------------+-------------------------------------------------------+
| Variable_name            | Value                                                 |
+--------------------------+-------------------------------------------------------+
| character_set_client     | utf8                                                  |
| character_set_connection | utf8                                                  |
| character_set_database   | utf8                                                  |
| character_set_filesystem | binary                                                |
| character_set_results    | utf8                                                  |
| character_set_server     | utf8                                                  |
| character_set_system     | utf8                                                  |
| character_sets_dir       | /opt/udb/program/mysql/percona-5.6.21/share/charsets/ |
+--------------------------+-------------------------------------------------------+
8 rows in set (0.00 sec)

MySQL [superset]> \nCREATE TABLE clusters (\n\tcreated_on DATETIME NOT NULL, \n\tchanged_on DATETIME NOT NULL, \n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\tcluster_name VARCHAR(250), \n\tcoordinator_host VARCHAR(255), \n\tcoordinator_port INTEGER, \n\tcoordinator_endpoint VARCHAR(255), \n\tbroker_host VARCHAR(255), \n\tbroker_port INTEGER, \n\tbroker_endpoint VARCHAR(255), \n\tmetadata_last_refreshed DATETIME, \n\tcreated_by_fk INTEGER, \n\tchanged_by_fk INTEGER, \n\tPRIMARY KEY (id), \n\tUNIQUE (cluster_name), \n\tFOREIGN KEY(created_by_fk) REFERENCES ab_user (id), \n\tFOREIGN KEY(changed_by_fk) REFERENCES ab_user (id)\n)\n\n;

发现建表成功了
总结一下：有过几次经历吧
@1 要么改变表的字段大小，
@2 要么升级mysql，
@3 要么尝试改变不同的字符集