Skip to content

Instantly share code, notes, and snippets.

@hooopo

hooopo/mb4.rb Secret

Created November 28, 2015 06:41
  • Star 1 You must be signed in to star a gist
  • Fork 2 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save hooopo/9310986e87200828e5e9 to your computer and use it in GitHub Desktop.
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
@qwlong
Copy link

qwlong commented Dec 14, 2016

sql << " DEFAULT #{column.inspect}" if column.default 应该修改为: sql << " DEFAULT #{column.default}" if column.default

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment