通过为某些列分配唯一的id来规范mysql数据库,然后将相关信息移动到新表中

这可以用3个SQL查询来完成:

INSERT INTO company (companyname)
SELECT DISTINCT companyname
FROM originalTable;

INSERT INTO store (storelocation, storenumber, companyid)
SELECT DISTINCT o.storelocation, o.storenumber, c.companyid
FROM originalTable o
JOIN company c ON o.companyname = c.companyname;

INSERT INTO item (itemname, itemmodel, storeid)
SELECT o.itemname, o.itemmodel, s.storeid
FROM originalTable o
JOIN store s ON s.storelocation = o.storelocation AND s.storenumber = o.storenumber
JOIN company c ON s.companyid = s.companyid AND o.companyname = c.companyname;

加入 company 在第三查询是必要的,如果原来的表可以重复的位置+存储从不同的公司存储。