FWD:MySQL changing ROW FORMAT to DYNAMIC or COMPRESSED

在MySQL5.6裡面，設置了innodb_large_prefix=ON、innodb_file_format=barracuda、innodb_file_per_table=ON ，且Innodb表的存儲格式為 DYNAMIC 或 COMPRESSED，則首碼索引最多可包含3072個位元組，首碼索引也同樣適用。

在MySQL5.7裡預設 innodb_large_prefix=1 解除了767bytes長度限制，但是單列索引長度最大還是不能超過3072bytes

 

-

changing ROW FORMAT to DYNAMIC or COMPRESSED

mysql> ALTER TABLE test ROW_FORMAT=DYNAMIC;

 

To convert all tables in a given database, here is a short bash script to do it:

 

#!/bin/bash

 

 

DATABASE=some_db

 

ROW_FORMAT=DYNAMIC

#ROW_FORMAT=COMPRESSED

 

TABLES=$(echo SHOW TABLES | mysql -s $DATABASE)

 

for TABLE in $TABLES ; do

    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT;"

    echo "ALTER TABLE $TABLE ROW_FORMAT=$ROW_FORMAT" | mysql $DATABASE

done

 

*注意這邊指的是ROW FORMAT而非MySQL Replication相關的binlog_format

 

Ref

http://blog.51cto.com/lee90/2087122

https://lxadm.com/MySQL:_changing_ROW_FORMAT_to_DYNAMIC_or_COMPRESSED