ActiveRecord::StatementInvalid: Mysql2::Error: Specified key was too long; max key length is 767

最新推荐文章于 2023-12-11 01:11:56 发布

小毒物儿

最新推荐文章于 2023-12-11 01:11:56 发布

阅读量407

点赞数

分类专栏： rails

本文链接：https://blog.csdn.net/noob_cherry/article/details/79131752

版权

rails 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

我的项目是为了支持emoj表情包，数据库需要设计成utf8m64,这样添加索引的大小最多为191

执行以下任务修改index的长度即可，如果之前使用的是255,会造成数据的截断，慎重哦。。。。。

desc "Database related tasks"

namespace :database do
desc "Convert to utf8mb4"
task convert_to_utf8mb4: :environment do
    connection = ActiveRecord::Base.connection
    database = connection.current_database

    connection.execute "ALTER DATABASE `#{database}` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"
    puts "Converted #{database} character set"

    # Change all tables
    connection.tables.each do |table|
      connection.columns(table).each do |column|
        if column.sql_type == "varchar(255)"
          puts "#{column.name} is varchar(255)"
          # Check for 255 indexed columns
          connection.indexes(table).each do |index|
            if index.columns.include?(column.name)
              puts "#{column.name} has index, altering length..."
              sql = "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` varchar(191)"
              sql << " NOT NULL" if not column.null
              sql << " DEFAULT #{column.inspect}" if column.default
              sql << ';'
              connection.execute sql
              puts "...done!"
            end
          end
        end
      end

      puts "Converting #{table}..."
      connection.execute "ALTER TABLE #{table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
      puts "...#{table} converted!"

      connection.columns(table).each do |column|
        if column.type == :string || column.type == :text
          puts "Converting #{column.name}..."
          sql = "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` #{column.sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci"
          sql << " NOT NULL" if not column.null
          sql << " DEFAULT #{column.inspect}" if column.default
          sql << ';'
          connection.execute sql
          puts "...#{column.name} done!"
        end
      end

      puts "Repairing #{table}..."
      connection.execute "REPAIR TABLE #{table};"
      puts "...#{table} repaired!"
      puts "Optimizing #{table}..."
      connection.execute "OPTIMIZE TABLE #{table};"
      puts "...#{table} optimized!"
    end
end
end