尝试将现有生产数据库表列从 enum 转换为 VARCHAR
我有一个问题，需要我将现有的实时生产（我已经在本地开发盒上复制了架构，不用担心）表列类型从枚举转换为字符串。
背景：
基本上，以前的开发人员让我的代码库处于绝对混乱状态：迁移版本非常过时，显然他在开发的某个时间点之后从未使用过它，现在我的任务是迁移 Rails 1.2.6应用到 2.3.5。
我无法让测试在 2.3.5 上正常运行，因为我的表列具有 ENUM 列类型，并且它们转换为 :string, :limit =>我的 schema.rb 上的 0 会在执行 rake db:test:prepare 时产生无效默认值的问题，例如：
Mysql::Error: Invalid default value for 'own_vehicle': CREATE TABLE `lifestyles` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `member_id` int(11) DEFAULT 0 NOT NULL, `own_vehicle` varchar(0) DEFAULT 'Y' NOT NULL, `hobbies` text, `sports` text, `AStar_activities` text, `how_know_IRC` varchar(100), `IRC_referral` varchar(200), `IRC_others` varchar(100), `IRC_rdrive` varchar(30)) ENGINE=InnoDB
我正在考虑编写迁移检查所有数据库表中是否有带有枚举的列并将其替换为 VARCHAR 的任务，我想知道这是否是解决此问题的正确方法。或者更好的是，如果有一种方法可以解决这个问题而无需修改数据库，那就更好了！
我也不知道如何编写它，以便它循环遍历我的数据库表并用 VARCHAR 替换所有 ENUM colum_types 。
参考文献
I have a problem that needs me to convert my existing live production (I've duplicated the schema on my local development box, don't worry) table column types from enums to a string.
Background:
Basically, a previous developer left my codebase in absolute disarray: migration versions are extremely out of date, and apparently he never used it after a certain point of time in development and now that I'm tasked with migrating a Rails 1.2.6 app to 2.3.5.
I can't get the tests to run properly on 2.3.5 because my table columns have ENUM column types and they convert to :string, :limit => 0 on my schema.rb which creates the problem of an invalid default value when doing a rake db:test:prepare, like in the case of:
Mysql::Error: Invalid default value for 'own_vehicle': CREATE TABLE `lifestyles` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `member_id` int(11) DEFAULT 0 NOT NULL, `own_vehicle` varchar(0) DEFAULT 'Y' NOT NULL, `hobbies` text, `sports` text, `AStar_activities` text, `how_know_IRC` varchar(100), `IRC_referral` varchar(200), `IRC_others` varchar(100), `IRC_rdrive` varchar(30)) ENGINE=InnoDB
I'm thinking of writing a migration task that looks through all the database tables for columns with enum and replace it with VARCHAR and I'm wondering if this is the right way to approach this problem. Or better yet if there is a way to fix this without having to modify the database, even better!
I'm also not sure how to write it so that it would loop through my database tables and replace all ENUM colum_types with a VARCHAR.
References
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我也在寻找一种解决方案来修复枚举的 schema.rb 文件，因为 RoR（目前版本 3.1.3）将它们称为限制为 0 的字符串。
看起来有一个插件/gem 用于处理枚举类型而无需更改数据库。在 Rails 1 和 2 上有一个插件： http://enum-column.rubyforge.org/
在 Rails 3 上，有一个与此类似的 gem： https://github.com/electronick/enum_column
之后您将 enum_column3 添加到 Gemfile（并运行捆绑更新），或安装插件，然后运行 rake 任务 db:schema:dump 以重新生成 schema.rb 文件。
我认为这个插件/gem 的唯一缺点是您必须将应用程序中使用这些字段的代码（因此您必须找到所有内容）从字符串更改为符号，并将 validates_inclusion_of 更改为 validates_columns。尽管如此，我还是没花太长时间就用 grep 和 vi 完成了这项工作。
或者，您可以按照 Robert 上面所说的那样直接更改 MySQL，使其符合 RoR 的预期。但我个人更喜欢继续使用 Enum 类型。 （我有 LAMP 背景。）将 enum 视为 varchar，在模型中使用 validates_inclusion_of，对于不使用 schema.rb 的所有内容都非常有效。只是测试和迁移有问题。
或者，您可以继续手动编辑 schema.rb 文件来修复限制。 （这就是我们到目前为止一直在做的事情，但这很烦人。）
（我想我没有足够的代表点来添加评论？史蒂夫的回答让我很恼火。你肯定想要使用迁移，因为这样你就保证做开发中的更改与生产中的更改相同，相信我，根据经验，迁移是保存更改的标准位置。如果您从较旧的备份中恢复，您可能需要重新应用它们。）
I was also looking for a solution to fix the schema.rb file for enums too, since RoR (version 3.1.3 at the moment) calls them string with limit 0.
It looks like there's a plugin/gem for handling enum types without changing the database. On Rails 1 and 2 a plugin: http://enum-column.rubyforge.org/
On Rails 3 there's a gem similar to that one: https://github.com/electronick/enum_column
After you add enum_column3 to your Gemfile (and run bundle update), or install the plugin, then run the rake task db:schema:dump to regenerate the schema.rb file.
The only downside of this plugin/gem that I see is you have to change code in your app that uses those fields (so you have to find it all) from strings to symbols, and the validates_inclusion_of to validates_columns. Still, it didn't take me too long to do this with grep and vi.
Alternatively you could do as Robert said above and alter the MySQL directly so it matches what RoR expects. But I personally prefer to keep using Enum types. (I have a LAMP background.) Treating enum as a varchar, using validates_inclusion_of in the model, works great for everything that doesn't use schema.rb. Just tests and migrations have a problem.
Or alternatively, you could keep manually editing the schema.rb file to fix the limit. (That's what we've been doing so far, but it's annoying.)
(I guess I don't have enough rep points to add comments? Steve's answer irks me. You definitely DO want to use migrations, because then you guarantee doing the same alterations on dev as you do in production. It's too easy to make a mistake even in copy-paste of the SQL during deployment. Believe me, I know this by experience. Plus the migrations are a standard place to keep the alterations. And you might need to re-apply them if you restore from an older backup.)