如何在mysql中更改ID [英] How to change ID in mysql
问题描述
假设我在 mySQL 中有一个表 Business.
Say I have a table Business in mySQL.
每个商家"中的 ID 是名称和位置的函数.它使编程变得更加容易.
The ID in each "business" is a function of title and location. It makes programming much more easy.
我也经常合并 2 个相同的企业，等等.
I also often merge 2 identical businesses, etc.
假设有一天公司名称发生变化，我想更改 ID.我很少做的手术.
Say one day the title of the business change and I want to change the ID. An operation that I will rarely done.
许多其他表格都指向该业务.
Many other tables point to that business.
那我该怎么办?
我是否应该创建新 ID，然后将指向原始业务的所有关系更改为新 ID?
Should I create the new ID and then change all relationship that points to the original business to the new ID?
是否有可以更快地执行此操作的 mysql 命令?
Is there a mysql command that'll do this faster?
任何示例 vb.net 代码?
Any sample vb.net code?
推荐答案
根据每个表使用的数据库引擎及其设计，您可能不需要做任何事情如果您已经配置了外部表上的键和约束.
Depending on the DB engine in use for each table and their design, you may not have to do anything if you have configured foreign keys and constraints on the tables.
首先要做的是检查链接表的结构.为此，请在 MySQL 会话中运行 show create table xyz 并检查:
The first thing to do would be to check the structure of the linked tables. To do this, run show create table xyz within a MySQL session and check:
- 如果正在使用 InnoDB 引擎
- 表中存在外键和约束
(外键仅在使用 InnoDB 引擎时才真正有用.您可以在其他引擎中请求它们，但它们不会做任何事情.)
(Foreign keys are only of any real use when using the InnoDB engine. You can request them in other engines but they won't do anything.)
如果您有幸使用了 InnoDB 引擎并且有适当的约束，那么您正在寻找类似的东西:
If you're lucky to be using the InnoDB engine and have constraints in place, you're looking for something like:
CONTRAINT `FK_businessID` FOREIGN KEY(`businessID`) REFERENCES `Business` (`ID`)
ON DELETE CASCADE ON UPDATE CASCADE
在表定义中.如果您确实看到了类似的情况，您应该能够安全地更改业务表中的 ID，并且所有其他链接表都应该效仿.
in the table definition. If you do see something like this, you should be able to safely change the ID in the business table and all the other linked tables should follow suit.
如果您没有外键但正在使用 InnoDB 引擎，请在单个事务中更改所有表中的信息，以便在发生任何错误时在您的外部进行更改控制(连接丢失、查询错误等)，您可以回滚.
If you don't have foreign keys but are using the InnoDB engine, change the information in all the tables within a single transaction so that, if anything does go wrong doing the changes outside of your control (connection lost, query error etc) you can roll back.
最后，如果您没有使用 InnoDB 引擎，祝您好运.您将不得不冒着即时"更改记录的风险.您的主要选择(按个人喜好排序)是:
Finally, if you're not using the InnoDB engine, good luck. You're going to have to risk changing records essentially 'on the fly'. Your main choices (in order of personal preference) are:
- 计算出新密钥并对表进行多次更新，希望它们都能正常工作并且不会影响当前使用数据的任何人
- 使用新 ID 创建新记录，将所有信息从初始记录复制到新记录，然后对链接表进行多次更新，然后删除原始记录，同时希望一切正常，不会影响当前任何人使用数据
- 用新的 ID 创建一个新记录，复制链接表中的所有记录，检查它，然后删除初始记录
- 使用魔法
显然，根据上述所有内容，在下定决心之前，请进行大量(我说的是大量)测试和备份.并在使用数据库的客户数量最少的时候进行；换句话说，一夜之间.
Obviously, with all of the above, do lots (and I'm talking lots) of testing and backups before making your mind up. And do it at a time when the minimum number of customers are using the DB; in other words, overnight.
这篇关于如何在mysql中更改ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
