MySQL utf8からutf8mb4への変換

絵文字など使う場合には文字コードをutf8mb4にする必要がある

my.cnfを修正

innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda

RDSの場合はparameter groupを修正

パラメータ説明

innodb_large_prefix

そのまま、alterで文字コードを変えようとするとエラーが出る

alter table user_messages convert to character set utf8mb4;

エラー

Index column size too large. The maximum column size is 767 bytes.

mysqlのデフォルトの限界に引っかかる。varchar(255)とかのutf8のカラムがあると、utf8mb4は4byte文字なので、256 x 4 で767を超える。

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

この二つは、innodb_large_prefixを有効にする為に必要。

対応順序

1 my.cnf修正
2 mysqld restart
3 (railsなどアプリ側でmigrateなど行うアプリケーションの場合は、migrate時にrow_formatを指定するように修正)

ActiveSupport.on_load :active_record do
  module ActiveRecord::ConnectionAdapters

    class AbstractMysqlAdapter
      def create_table_with_innodb_row_format(table_name, options = {})
        table_options = options.merge(:options => 'ENGINE=InnoDB ROW_FORMAT=DYNAMIC')
        create_table_without_innodb_row_format(table_name, table_options) do |td|
          yield td if block_given?
        end
      end
      alias_method_chain :create_table, :innodb_row_format
    end

  end
end

5 データベースの文字コードを修正する

alter database <DBNAME> character set utf8mb4;

6 既存のrow_formatデータを修正する

alter table <TABLENAME> ROW_FORMAT=DYNAMIC;

7 既存のテーブルの文字コードをutf8mb4に修正する

alter table <TABLENAME> convert to character set utf8mb4;

便利なワンライナー

(mysql -uroot -p <DATABASE_NAME> -e "show tables" --batch --skip-column-names | xargs -I{} echo 'alter table `'{}'` convert to character set utf8mb4;') > /tmp/alters.sql