mysql - Rails 5 Mysql UUID

Question

发现rails 5有一个原生的uuid integration ，想尝试一下，但出现此错误:

== 20170330041631 EnableUuidExtension: migrating ==============================
-- enable_extension("uuid-ossp")
  -> 0.0000s
== 20170330041631 EnableUuidExtension: migrated (0.0001s) =====================

== 20170331035925 CreateUsers: migrating ======================================
-- create_table(:users, {:id=>:uuid})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1: CREATE TABLE `users` (`id` uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1: CREATE TABLE `users` (`id` uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest` varchar(255), `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL) ENGINE=InnoDB
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'uuid PRIMARY KEY, `name` varchar(255), `username` varchar(255), `password_digest' at line 1
/home/zetacu/projects/rails-5-test/db/migrate/20170331035925_create_users.rb:3:in `change'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `load'
/home/zetacu/.rbenv/versions/2.4.0/bin/bundle:22:in `<main>'
Tasks: TOP => db:migrate

这是根据帖子进行的迁移:

class EnableUuidExtension < ActiveRecord::Migration[5.0]
  def change
    enable_extension 'uuid-ossp'
  end
end


class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users, id: :uuid do |t|
      t.string :name
      t.string :username
      t.string :password_digest

      t.timestamps
    end
  end
end

在 application.rb 上:

config.generators do |g|
  g.orm :active_record, primary_key_type: :uuid
end

我缺少什么？Rails-5 支持 mysql 还是必须像 Rails-4 一样手动？

create_table :users, id: false do |t|
  t.string :uuid, limit: 36, primary: true, null: false
  ...

gem 版本:

rails (~> 5.0.2)
mysql2 (>= 0.3.18, < 0.5)

Answer 1

我的回答是对@santosh 回答的更新。我正在整合此处描述的所有最佳实践:

• https://www.percona.com/blog/2014/12/19/store-uuid-optimized-way/
• http://mysqlserverteam.com/storing-uuid-values-in-mysql-tables/

我正在使用 simple_uuid gem，因为它可以生成“v1”UUID。 Ruby 的内置 SecureRandom.uuid 生成 v4。我们需要 v1，因为它将时间戳作为 UUID 的一部分。阅读上面的链接以获得更深入的了解。 MySQL 的 UUID() 函数生成 v1 UUID。

app/models/concerns/binary_uuid_pk.rb

module BinaryUuidPk
  extend ActiveSupport::Concern

  included do
    before_validation :set_id, on: :create
    validates :id, presence: true
  end

  def set_id
    uuid_object = SimpleUUID::UUID.new
    uuid_string = ApplicationRecord.rearrange_time_of_uuid( uuid_object.to_guid )
    uuid_binary = ApplicationRecord.id_binary( uuid_string )
    self.id = uuid_binary
  end

  def uuid
    self[:uuid] || (id.present? ? ApplicationRecord.format_uuid_with_hyphens( id.unpack('H*').first ).upcase : nil)
  end


  module ClassMethods
    def format_uuid_with_hyphens( uuid_string_without_hyphens )
      uuid_string_without_hyphens.rjust(32, '0').gsub(/^(.{8})(.{4})(.{4})(.{4})(.{12})$/, '\1-\2-\3-\4-\5')
    end

    def rearrange_time_of_uuid( uuid_string )
      uuid_string_without_hyphens = "#{uuid_string[14, 4]}#{uuid_string[9, 4]}#{uuid_string[0, 8]}#{uuid_string[19, 4]}#{uuid_string[24..-1]}"
      ApplicationRecord.format_uuid_with_hyphens( uuid_string_without_hyphens )
    end

    def id_binary( uuid_string )
      # Alternate way: Array(uuid_string.downcase.gsub(/[^a-f0-9]/, '')).pack('H*')
      SimpleUUID::UUID.new( uuid_string ).to_s
    end

    def id_str( uuid_binary_string )
      SimpleUUID::UUID.new( uuid_binary_string ).to_guid
    end

    # Support both binary and text as IDs
    def find( *ids )
      ids = [ids] unless ids.is_a?( Array )
      ids = ids.flatten

      array_binary_ids = ids.each_with_object( [] ) do |id, array|
        case id
          when Integer
            raise TypeError, 'Expecting only 36 character UUID strings as primary keys'
          else
            array <<  SimpleUUID::UUID.new( id ).to_s

        end
      end

      super( array_binary_ids )
    end
  end
end

app/models/application_record.rb

## ApplicationRecord (new parent of all models in Rails 5)
class ApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  include BinaryUuidPk
end

现在，所有模型都将支持优化的 UUID 主键。

示例迁移

class CreateUserProfiles < ActiveRecord::Migration[5.0]
  def change
    create_table :user_profiles, id: false do |t|
      t.binary :id, limit: 16, primary_key: true, null: false
      t.virtual :uuid, type: :string, limit: 36, as: "insert( insert( insert( insert( hex(id),9,0,'-' ), 14,0,'-' ), 19,0,'-' ), 24,0,'-' )"
      t.index :uuid, unique: true

      t.string :name, null: false
      t.string :gender, null: false
      t.date :date_of_birth
      t.timestamps null: false
    end

    execute <<-SQL
      CREATE TRIGGER before_insert_user_profiles
        BEFORE INSERT ON user_profiles
        FOR EACH ROW
        BEGIN
          IF new.id IS NULL THEN
            SET new.id = UUID_TO_BIN(uuid(), 1);
          END IF;
        END
    SQL
  end
end

将 UUID_TO_BIN() 函数添加到 MySQL 数据库:

DELIMITER //
CREATE FUNCTION UUID_TO_BIN(string_uuid BINARY(36), swap_flag INT)
        RETURNS BINARY(16)
        LANGUAGE SQL  DETERMINISTIC  CONTAINS SQL  SQL SECURITY INVOKER
      RETURN
        UNHEX(CONCAT(
            SUBSTR(string_uuid, 15, 4),
            SUBSTR(string_uuid, 10, 4),
            SUBSTR(string_uuid,  1, 8),
            SUBSTR(string_uuid, 20, 4),
            SUBSTR(string_uuid, 25) ));
//
DELIMITER ;

上述功能是MySQL 8.0及以上版本内置的。在撰写本文时，8.0 尚未正式发布。所以，我现在要添加这个功能。但我保持函数签名与 MySQL 8.0 中的相同。因此，当我们迁移到 8.0 时，我们所有的迁移和触发器仍然有效。