MySQL 迁移脚本帮助
我正在开发一个列出各种餐馆目录的网站,目前正在切换到较新的 CMS。我遇到的问题是两个 CMS 代表的餐厅数据不同。
旧 CMS
交叉引用数据库,因此它可能会列出如下示例的条目:
ID / FieldID / ItemID / data
3 / 1 / 6 / 123 Foo Street
4 / 2 / 6 / Bar
将 FieldID 1 引用为街道的一个引用表,FieldID 2 作为城市。
另一个参考表将 ItemID 6 引用为 Delicious Restaurant。
新 CMS
当我设置示例列表时,数据库在新 CMS 上的方式是所有直接行,没有交叉引用。因此,同一家餐厅的数据将是:
ID / Name / Street / City
3 / Delicious Restaurant / 123 Foo Street / Bar
大约有 2,000 个餐厅列表,因此就 SQL 行数据大小而言,数量并不是很大,但当然,甚至不需要考虑手动重新输入所有餐厅列表。
我有一些想法,但它会非常脏并且需要一段时间,而且我不是 MySQL 专家,所以我来这里是为了一些我应该如何解决它的想法。
非常感谢那些能够提供帮助的人。
I am working on a site that lists a directory of various restaurants, and currently in the process of switching to a newer CMS. The problem I have is that both CMSes represent the restaurant data differently.
Old CMS
A Cross Reference Database so it may list an entry for an example like this:
ID / FieldID / ItemID / data
3 / 1 / 6 / 123 Foo Street
4 / 2 / 6 / Bar
One reference table that reference FieldID 1 as street, FieldID 2 as City.
Another reference table that references ItemID 6 as Delicious Restaurant.
New CMS
The way the database is on the new CMS when I set up a sample listing, is all direct rows, no cross referencing. So instead the data for the same restaurant will be:
ID / Name / Street / City
3 / Delicious Restaurant / 123 Foo Street / Bar
There are about 2,000 restaurant listings so it's not a HUGE amount in terms of SQL row data size, but of course enough to not even consider re-entering all the restaurant listings by hand.
I have a few ideas, but it would be extremely dirty and take a while, and I'm not a MySQL expert so I am here for some ideas how I should tackle it.
Many thanks to those who can help.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您可以多次联接数据表以获得如下内容:
您没有提供所有表的名称,所以我编了一些名称。如果您需要提取更多字段,则扩展此类查询应该很简单。
You can join against the data table multiple times to get something like this:
You didn't provide names for all of the tables, so I made some names up. If you have more fields that you need to extract, it should be trivial to extend this sort of query.