MySQL 从现有表中的现有列创建数据透视表

Question

我有一个预先存在的表，其中包含各种客户信息。目前它还有“城市”以及“地区”和“州”作为字符串列在 3 列中。冗余信息!

我想创建三个新表，一张用于城市，一张用于地区，一张用于州，其中将包含每个城市等的单个条目，然后将 ID 引用回现有客户带有 location_id 的表。

我如何将不同的城市名称导出到城市表中，将不同的地区导出到地区表中，然后让城市引用region_id和state_id表，以便将信息全部分组!

当然是业余问题，但我感谢任何帮助!

Answer 1

你不需要三张不同的 table !您需要一张包含三列的表:city、state、region。

原因是city本身并不存在。考虑(在美国)伊利诺伊州斯普林菲尔德。还有马萨诸塞州斯普林菲尔德。或者佛罗里达州迈阿密和俄亥俄州迈阿密。您拥有的是具有层次结构的数据维度。存储此信息的正确方法是在最低级别(在您的情况下是城市)，并使用提供其他信息的“维度”表。

假设你的原始数据是正确的，你可以这样做:

create table Cities (
    CityId int auto_increment not null primary key,
    City varchar(255),
    State varchar(255),
    Region varchar(255)
);

insert into Cities(City, State, Region)
    select distinct City, State, Region
    from YourTable;

我意识到这不是“标准范式”。但对于大多数应用程序来说，这种方法效果很好。例如，如果您要为要从列表中选择状态的应用程序执行此操作，请在状态上创建索引，查询将会很快。

在某些情况下，您可能需要在州和地区级别使用单独的表。如果您在这些级别上有许多不同的列，就会出现这种情况。特别是，如果您修改这些列中的值。当数据是静态的(城市不经常改变状态)时，扁平维度(例如此处描述的)是最合适的。当您更改不同级别的值时，标准化是最合适的。