Rails 5 Mysql UUID

Question

发现rails 5有一个本机uuid集成，想试一试，但我得到了这个错误：

== 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

将函数添加到MySQL DB

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还不是GA。所以，我现在要添加这个函数。但我将函数签名与MySQL 8.0中的签名保持相同。因此，当我们移动到8.0时，我们所有的迁移和触发都将仍然有效。

Answer 2

Din没有找到任何关于mysql/rails-5 uuid集成的文档，最后我这样做了：

## Migration
class CreateUsers < ActiveRecord::Migration[5.0]
  def change
    create_table :users, id: false do |t|
      t.string :id, limit: 36, primary_key: true, null: false 
      t.string :name
      t.string :username
      t.string :password_digest
      t.timestamps
    end
  end
end


#user model
class User < ApplicationRecord
  before_create :set_uuid

  def set_uuid
    self.id = SecureRandom.uuid
  end
end

它可以工作，但它是跳到使用“神奇的rails”解决方案来自动处理uuid和关系。

Answer 3

我的回答是更新@zetacu的答案。它非常适合rails 5.0.2中的MySQL。

  ## Model
   class Tip < ActiveRecord::Base
     before_validation :set_uuid, on: :create
     validates :id, presence: true

     def set_uuid
       self.id = SecureRandom.uuid
     end

   end

    ## Migration
    class CreateTip < ActiveRecord::Migration[5.0]
      def change
        create_table :tips, id: false, force: true do |t|
          t.string :id, :limit => 36, :primary_key => true
          t.string :title, :null => false, :default => ""
          t.text :description

          t.timestamps
        end
      end
    end