Mysql2::错误: 'column_name' 的默认值无效

Question

嘿，我有一个 Rails 方案，我试图在 mysql 中创建一个数据库。我想用这个命令从方案中重新加载数据库:

rails db:schema:load

其他表创建成功，但是这个有默认值的表有问题:

create_table "settings", force: :cascade do |t|
    t.integer  "user_id"
    t.string   "legal_columns_order",     default: "id,indicator,classification,urgency,package,registrar,subset_type,creation_time,sender,conjunctions,followings,responses,letter_receivers,letter_date,subject,letter_number,description,recipient,receiving_type,person_name,tel_number,portal_number,operator,transcriptions"
    t.string   "legal_columns_active",    default: "true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true"
    t.string   "official_columns_order",  default: "id,indicator,classification,urgency,recipient,package,creation_time,sender,registrar,subset_type,conjunctions,followings,responses,letter_receivers,letter_date,subject,letter_number,barcode,description,receiving_type,transcriptions"
    t.string   "official_columns_active", default: "true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true"
    t.string   "panel_column_order",      default: "{\"panel_1\":[\"letterType\",\"subject\",\"subjectSugestion\",\"letterNumber\",\"classification\",\"sender\",\"urgency\",\"receivers\",\"letterDate\"],\"panel_2\":[\"recipient\",\"recivingType\",\"creation_time\",\"Packageid\",\"barcode\",\"Scan\"],\"panel_3\":[\"following\",\"conjunction\",\"response\",\"transcriptions\",\"description\",\"enclosed\",\"person_name\",\"tel_number\",\"portal_number\",\"operator\"],\"panel_names\":{\"panel_1\":\"اطلاعات اصلی\",\"panel_2\":\"اطلاعات ثبتی\",\"panel_3\":\"اطلاعات تکمیلی\"}}"
    t.string   "package_panel_columns",   default: "{\"panel_1\":[\"courier_company\",\"classification\",\"receiving_type\",\"courier_type\",\"first_barcode\",\"second_barcode\",\"post_receiving_date\",\"creation_time\",\"registrar\"],\"panel_2\":[\"sender\",\"letter_receivers\",\"recipient_unit\",\"export_date\",\"subject\",\"recipient\",\"description\"],\"panel_names\":{\"panel_1\":\"اطلاعات اصلی\",\"panel_2\":\"اطلاعات ثبتی\"}}"
    t.string   "package_columns_order",   default: "id,courier_company,classification,registrar,post_receiving_date,receiving_type,courier_type,first_barcode,sender,letter_receivers,export_date,subject,recipient,description,creation_time,recipient_unit"
    t.string   "package_columns_active",  default: "true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true"
    t.string   "form_panel_columns",      default: "{\"panel_1\":[\"formType\",\"sender\",\"subject\",\"receivers\",\"creationDate\",\"receptionDate\",\"registrar\"],\"panel_2\":[\"recipient\",\"recipient_unit\",\"attachments\",\"documentNumber\",\"deliveryDate\",\"description\"],\"panel_names\":{\"panel_1\":\"اطلاعات اصلی\",\"panel_2\":\"اطلاعات تکمیلی\"}}"
    t.string   "form_columns_order",      default: "id,form_type,sender,subject,registrar,form_receivers,creation_date,reception_date,recipient,document_number,delivery_date,description,recipient_unit"
    t.string   "form_columns_active",     default: "true,true,true,true,true,true,true,true,true,true,true,true,true"
    t.string   "tracking_column_order",   default: "registrar,package,classification,indicator,letter_urgency,sender,subset_type,letter_created_at,assignee,paraph,tracking_type,tracking_urgency,tracking_created_at"
    t.string   "tracking_column_active",  default: "true,true,true,true,true,true,true,true,true,true,true,true,true"
    t.datetime "created_at",                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      null: false
    t.datetime "updated_at",                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      null: false
    t.index ["user_id"], name: "index_settings_on_user_id"
  end

我遇到了这个错误:

rails aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Invalid default value for 'legal_columns_order': CREATE TABLE `settings`

你有什么想法吗？

Answer 1

t.string 创建一个 VARCHAR(255) 列，最大长度为 255 个字符。但是您的默认长度是 269 个字符。参见 NATIVE_DATABASE_TYPES在 Rails 的源代码中:

  NATIVE_DATABASE_TYPES = {
    primary_key: "bigint auto_increment PRIMARY KEY",
    string:      { name: "varchar", limit: 255 },
    text:        { name: "text", limit: 65535 },
    integer:     { name: "int", limit: 4 },
    float:       { name: "float" },
    decimal:     { name: "decimal" },
    datetime:    { name: "datetime" },
    timestamp:   { name: "timestamp" },
    time:        { name: "time" },
    date:        { name: "date" },
    binary:      { name: "blob", limit: 65535 },
    boolean:     { name: "tinyint", limit: 1 },
    json:        { name: "json" },
  }

首先，您必须调查 255 个字符的限制在您的应用程序上下文中是否可行。它看起来不像，但也许使用较短的默认值是一种选择。

当您需要存储更长的文本时，您有两个选择:

1. 使用 t.text 而不是 t.string 支持更长的文本(默认为 65535 但可以配置为存储MB 的数据)。但不幸的是，这种数据类型不支持默认值(BLOB and TEXT columns cannot have DEFAULT values.)。您必须改为在模型中设置默认值。

2. 或者您在该列上手动设置一个更高的限制 - 例如 2048 个字符(明智地选择):

   t.string "legal_columns_order", limit: 2_048, default: ...    
   

我建议使用 TEXT 列类型并处理应用程序中的默认值，因为这使得以后更改默认文本时更容易。

要处理模型中的默认值，我会这样做:

after_initialize :set_defaults

private 
def set_defaults
  self.legal_columns_order  ||= "id,indicator,classification,urgency,package,registrar,subset_type,creation_time,sender,conjunctions,followings,responses,letter_receivers,letter_date,subject,letter_number,description,recipient,receiving_type,person_name,tel_number,portal_number,operator,transcriptions"
  self.legal_columns_active ||= "true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true,true"
  # ...
end