Rails 5 Mysql UUID [英] Rails 5 Mysql UUID
问题描述
发现Rails 5具有本机 uuid集成，想尝试一下，但出现此错误:
Found out that rails 5 has a native uuid integration, wanted to try it out but I'm getting this error:
== 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
这是根据帖子进行的迁移:
This are the migrations acoording to the post:
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中那样是手动的?
What I'm missing?, Does Rails-5 has mysql support or has to be manual like in 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)
推荐答案
我的答案是@santosh答案的更新.我将结合此处描述的所有最佳做法:
My answer is an update of @santosh's answer. I am incorporating all the best practices described here:
- https://www.percona.com /blog/2014/12/19/store-uuid-optimized-way/
- http://mysqlserverteam.com/storing-uuid-values-in -mysql-tables/
- 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.
I am using the simple_uuid gem because it can generate "v1" UUIDs. Ruby's built-in SecureRandom.uuid generates v4. We need v1, because that is what incorporates timestamp as part of the UUID. Read the links above to get a deeper understanding. MySQL's UUID() function generates v1 UUIDs.
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主键.
Now, all models will support optimized UUID primary keys.
示例迁移
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数据库:
Add UUID_TO_BIN() function to the 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尚未发布.因此，我现在要添加该功能.但是我保持了函数签名与MySQL 8.0相同.因此，当我们升级到8.0时，我们所有的迁移和触发器仍然可以使用.
The above function is built-in to MySQL 8.0 and above. At the time of writing, 8.0 is not yet GA. So, I am adding the function for now. But I have kept the function signature the same as what is there in MySQL 8.0 . So, when we move to 8.0, all our migrations and triggers would still work.
这篇关于Rails 5 Mysql UUID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
