MySQL Rails：将数据库中的空字符串强制为NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7202319/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails: Force empty string to NULL in the database
提问by Thilo
Is there an easy way (i.e. a configuration) to force ActiveRecord to save empty strings as NULL in the DB (if the column allows)?
有没有一种简单的方法（即配置）来强制 ActiveRecord 在数据库中将空字符串保存为 NULL（如果列允许）？
The reason for this is that if you have a NULLable string column in the DB without a default value, new records that do not set this value will contain NULL, whereas new records that set this value to the empty string will not be NULL, leading to inconsistencies in the database that I'd like to avoid.
这样做的原因是，如果数据库中有一个没有默认值的 NULLable 字符串列，则未设置此值的新记录将包含 NULL，而将此值设置为空字符串的新记录将不会是 NULL，导致我想避免的数据库不一致。
Right now I'm doing stuff like this in my models:
现在我在我的模型中做这样的事情：
before_save :set_nil
def set_nil
[:foo, :bar].each do |att|
self[att] = nil if self[att].blank?
end
end
which works but isn't very efficient or DRY. I could factor this out into a method and mix it into ActiveRecord, but before I go down that route, I'd like to know if there's a way to do this already.
这有效但不是很有效或 DRY。我可以将其分解为一个方法并将其混合到 ActiveRecord 中，但在我走这条路之前，我想知道是否已经有办法做到这一点。
采纳答案by dexter
Try if this gem works:
试试这个 gem 是否有效：
https://github.com/rubiety/nilify_blanks
https://github.com/rubiety/nilify_blanks
Provides a framework for saving incoming blank values as nil in the database in instances where you'd rather use DB NULL than simply a blank string...
In Rails when saving a model from a form and values are not provided by the user, an empty string is recorded to the database instead of a NULL as many would prefer (mixing blanks and NULLs can become confusing). This plugin allows you to specify a list of attributes (or exceptions from all the attributes) that will be converted to nil if they are blank before a model is saved.
Only attributes responding to blank? with a value of true will be converted to nil. Therefore, this does not work with integer fields with the value of 0, for example...
在您宁愿使用 DB NULL 而不是简单的空白字符串的情况下，提供了一个框架，用于在数据库中将传入的空白值保存为 nil...
在 Rails 中，当从表单中保存模型并且用户未提供值时，数据库中会记录一个空字符串，而不是许多人喜欢的 NULL（混合空白和 NULL 会变得混乱）。这个插件允许你指定一个属性列表（或所有属性的例外），如果在保存模型之前它们是空白的，这些属性将被转换为 nil。
只有属性响应空白？值为 true 时将转换为 nil。因此，这不适用于值为 0 的整数字段，例如...
回答by Simone Carletti
Yes, the only option at the moment is to use a callback.
是的，目前唯一的选择是使用回调。
before_save :normalize_blank_values
def normalize_blank_values
attributes.each do |column, value|
self[column].present? || self[column] = nil
end
end
You can convert the code into a mixin to easily include it in several models.
您可以将代码转换为 mixin，以便轻松地将其包含在多个模型中。
module NormalizeBlankValues
extend ActiveSupport::Concern
included do
before_save :normalize_blank_values
end
def normalize_blank_values
attributes.each do |column, value|
self[column].present? || self[column] = nil
end
end
end
class User
include NormalizeBlankValues
end
Or you can define it in ActiveRecord::Base to have it in all your models.
或者您可以在 ActiveRecord::Base 中定义它以在您的所有模型中使用它。
Finally, you can also include it in ActiveRecord::Base but enable it when required.
最后，您还可以将它包含在 ActiveRecord::Base 中，但在需要时启用它。
module NormalizeBlankValues
extend ActiveSupport::Concern
def normalize_blank_values
attributes.each do |column, value|
self[column].present? || self[column] = nil
end
end
module ClassMethods
def normalize_blank_values
before_save :normalize_blank_values
end
end
end
ActiveRecord::Base.send(:include, NormalizeBlankValues)
class User
end
class Post
normalize_blank_values
# ...
end
回答by troelskn
Another option is to provide custom setters, instead of handling this in a hook. E.g.:
另一种选择是提供自定义设置器，而不是在钩子中处理它。例如：
def foo=(val)
super(val == "" ? nil : val)
end
回答by serghei
My suggestion:
我的建议：
# app/models/contact_message.rb
class ContactMessage < ActiveRecord::Base
include CommonValidations
include Shared::Normalizer
end
# app/models/concerns/shared/normalizer.rb
module Shared::Normalizer
extend ActiveSupport::Concern
included do
before_save :nilify_blanks
end
def nilify_blanks
attributes.each do |column, value|
# ugly but work
# self[column] = nil if !self[column].present? && self[column] != false
# best way
#
self[column] = nil if self[column].kind_of? String and self[column].empty?
end
end
end
回答by Joe Half Face
Sorry for necroposting, but I didn't find exact thing here in answers, if you need solution to specifyfields which should be nilified:
很抱歉进行 necroposting，但我在答案中没有找到确切的内容，如果您需要解决方案来指定应取消的字段：
module EnforceNil
extend ActiveSupport::Concern
module ClassMethods
def enforce_nil(*args)
self.class_eval do
define_method(:enforce_nil) do
args.each do |argument|
field=self.send(argument)
self.send("#{argument}=", nil) if field.blank?
end
end
before_save :enforce_nil
end
end
end
end
ActiveRecord::Base.send(:include, EnforceNil)
This way:
这边走：
class User
enforce_nil :phone #,:is_hobbit, etc
end
Enforcing certain field is handy when let's say you have field1 and field2. Field1 has unique index in SQL, but can be blank, so you need enforcement(NULL considered unique, "" - not by SQL), but for field2 you don't actually care and you have already dozens of callbacks or methods, which work when field2 is "", but will dig your app under the layer of errors if field2 is nil. Situation I faced with.
当假设您有 field1 和 field2 时，强制执行某个字段很方便。Field1 在 SQL 中具有唯一索引，但可以为空，因此您需要强制执行（NULL 认为是唯一的，"" - 不是 SQL），但是对于 field2，您实际上并不关心，并且您已经有许多回调或方法，它们可以工作当 field2 为 "" 时，但如果 field2 为 ，则会在错误层下挖掘您的应用程序nil。我面临的情况。
May be useful for someone.
可能对某人有用。
回答by Joshua Pinter
Strip Attributes Gem
剥离属性宝石
There's a handy gem that does this automatically when saving a record, whether that's in a user form or in the console or in a rake task, etc.
有一个方便的 gem 可以在保存记录时自动执行此操作，无论是在用户表单中、控制台中还是在 rake 任务中，等等。
It's called strip_attributesand is extremely easy to use, with sane defaults right out of the box.
它被称为strip_attributes，非常易于使用，具有开箱即用的合理默认值。
It does two main things by default that should almostalways be done:
默认情况下，它几乎总是做两件主要的事情：
Strip leading and trailing white space:
" My Value " #=> "My Value"Turn empty Strings into
NULL:"" #=> NULL " " #=> NULL
去除前导和尾随空格：
" My Value " #=> "My Value"将空字符串转换为
NULL："" #=> NULL " " #=> NULL
Install
安装
You can add it to your gem file with:
您可以使用以下命令将其添加到您的 gem 文件中：
gem strip_attributes
gem strip_attributes
Usage
用法
Add it to any (or all) models that you want to strip leading/trailing whitespace from and turn empty strings into NULL:
将它添加到要从中去除前导/尾随空格并将空字符串转换为的任何（或所有）模型NULL：
class DrunkPokerPlayer < ActiveRecord::Base
strip_attributes
end
Advanced Usage
高级用法
There are additional options that you can pass on a per-Model basis to handle exceptions, like if you want to retain leading/trailing white space or not, etc.
您可以在每个模型的基础上传递其他选项来处理异常，例如是否要保留前导/尾随空格等。
You can view all of the options on the GitHub repository here:
您可以在此处查看 GitHub 存储库上的所有选项：
回答by daslicious
I use the attribute normalizergem to normalize attributes before they into the db.
我使用属性规范化gem 在属性进入数据库之前对其进行规范化。
