使用另一个表的记录规范 MySQL 表
我有 2 张桌子。城市表未标准化，因为国家/地区信息是纯文本形式。我已将 id_country 添加到“城市”表中（该列为空）。
我需要检查 city>country 和 country>country 之间的匹配，然后更新与国家/地区表中的 id_country 匹配的城市记录。最后，我将能够从城市表中删除“国家/地区”列。
城市表
- id_city (1, 2, 3...)
- 城市（华盛顿、瓜亚基尔、波恩...）
- 国家/地区（德国、厄瓜多尔、美国...）
- id_country（当前为空）
国家/地区表
- id_country (1, 2, 3...)
- 代码 (GE, EC, US...)
- 国家/地区（德国、厄瓜多尔、美国...）
我不知道从哪里开始以及是否可以可以做到使用 SQL 查询。我最初的想法是在 php 循环中搜索匹配项，但这似乎是一个非常困难的实现。
i have 2 tables. The city tables is not normalized because the country information is in plain text. I have added the id_country to the 'city' table (that column is empty).
I need to check for matches between city>country and country>country and then update the city records that matched with the id_country from the country table. At the end i will be able to delete the 'country' column from the city table.
City table
- id_city (1, 2, 3...)
- city (Washington, Guayaquil, Bonn...)
- country (Germany, Ecuador, USA...)
- id_country (currently empty)
Country table
- id_country (1, 2, 3...)
- code (GE, EC, US...)
- country (Germany, Ecuador, USA...)
I have no idea on where to start and if it can be done with a SQL query. My original idea was to search for matches in a php loop but that seems to be a really harder implementation.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以通过 UPDATE 语句上的 JOIN 来完成此操作。
使用
INNER JOIN将确保仅针对具有匹配国家/地区值的城市进行更新。运行后，您将能够选择所有 id_country 仍为空的城市，以防其中一些城市不匹配。相反，一旦确定所有城市都有 id_country，您就可以从城市表中删除该列。
You can do this with a JOIN on an UPDATE statement.
Using an
INNER JOINwill make sure that updates only occur for cities that have a matching country value.Once you've run it, you'll be able to select all those cities that still have a null id_country just in case some of them didn't match. Conversely, once you've determined that all your cities have an id_country, you can delete that column from the city table.
废话。规范化并不意味着“用 ID 号替换纯文本”。找到教你这个的人，用锋利的棍子戳他的眼睛。
您的真正问题是“城市”加“国家/地区”不足以识别城市，至少在美国是这样。我认为美国至少有十几个不同的城市以“华盛顿”命名。
与其用 ID 号替换国家/地区名称，不如用两个字母的国家/地区代码替换它。代码是人类可读的；在使用城市表的每个查询中，id 号都需要额外的 JOIN。
Nonsense. Normalization doesn't mean "replace plain text with id numbers". Find whoever taught you that and poke him in the eye with a sharp stick.
Your real problem is that "city" plus "country" isn't sufficient to identify cities, at least in the USA. I think there are at least a dozen different cities named "Washington" in the USA.
Instead of replacing the country name with an id number, you'd be far better off replacing it with the two-letter country code. The codes are human-readable; the id numbers will require an additional JOIN in every query that uses your table of cities.
像这样的东西应该有效：
Something like this should work: