修改mysql字段的方法

随笔记录

目录

0. 背景介绍

1. 以修改某字段（risk_conno）自增为例

2. 查看是否被锁

3. 解除  XX 表外键约束 即 删除外键约束

4. 添加自增属性

5. 添加外键约束

6. 解除锁定

7. 查看是否被锁

---

0. 背景介绍

mysql 表已创建后，后期因需求变更，修改某字段

1. 以修改某字段（risk_conno）自增为例

1. 锁表
# lock <table_Name 1> write, <table_Name 2> write;

lock tables Initapp_rule_acc_except write,Initapp_rule_account_config write,Initapp_rule_condition_config write,Initapp_rule_factor_config write,Initapp_rule_factor_param_config write,Initapp_rule_info write,Initapp_rule_relationship_config write,Initapp_rule_threshold_config write,Initapp_threshold_value write;

2. 查看是否被锁

# 查看表是否被锁 语句

# show OPEN TABLES WHERE In_use>0;

 表已锁

3. 解除  XX 表外键约束 即 删除外键约束

# 解除 ** 表外键约束 即 删除外键约束

# ALTER TABLE  <table_name> DROP FOREIGN KEY <foreign_key_name>;


ALTER TABLE Initapp_rule_acc_except DROP FOREIGN KEY Initapp_rule_acc_except_risk_conno_id_fa925afc_fk;

ALTER TABLE Initapp_rule_account_config DROP FOREIGN KEY Initapp_rule_account_config_risk_conno_id_207834c1_fk;

ALTER TABLE Initapp_rule_condition_config DROP FOREIGN KEY Initapp_rule_condition_config_risk_conno_id_8e059b3c_fk;

ALTER TABLE Initapp_rule_factor_config DROP FOREIGN KEY Initapp_rule_factor_config_risk_conno_id_15d1e0cd_fk;

ALTER TABLE Initapp_rule_factor_param_config DROP FOREIGN KEY Initapp_rule_factor_param_config_risk_conno_id_8bdb0506_fk;

ALTER TABLE Initapp_rule_relationship_config DROP FOREIGN KEY Initapp_rule_relationship_config_risk_conno_id_4e98035d_fk;

ALTER TABLE Initapp_rule_threshold_config DROP FOREIGN KEY Initapp_rule_threshold_config_risk_conno_id_aee39686_fk;

ALTER TABLE Initapp_threshold_value DROP FOREIGN KEY Initapp_threshold_value_risk_conno_id_44a8c74e_fk;

4. 添加自增属性

# alter table <表名> change <字段名> <字段名> int not NULL auto_increment;

ALTER table Initapp_rule_info change risk_conno risk_conno int not NULL auto_increment;

5. 添加外键约束

ALTER table Initapp_rule_acc_except add CONSTRAINT Initapp_rule_acc_except_risk_conno_id_fa925afc_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_account_config add CONSTRAINT Initapp_rule_account_config_risk_conno_id_207834c1_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_condition_config add CONSTRAINT Initapp_rule_condition_config_risk_conno_id_8e059b3c_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_factor_config add CONSTRAINT Initapp_rule_factor_config_risk_conno_id_15d1e0cd_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_factor_param_config add CONSTRAINT Initapp_rule_factor_param_config_risk_conno_id_8bdb0506_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_relationship_config add CONSTRAINT Initapp_rule_relationship_config_risk_conno_id_4e98035d_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_rule_threshold_config add CONSTRAINT Initapp_rule_threshold_config_risk_conno_id_aee39686_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

ALTER table Initapp_threshold_value add CONSTRAINT Initapp_threshold_value_risk_conno_id_44a8c74e_fk FOREIGN key(risk_conno_id) REFERENCES Initapp_rule_info(risk_conno);

6. 解除锁定

UNLOCK TABLES;

7. 查看是否被锁

查看语句：
# show OPEN TABLES WHERE In_use>0;

 

到此已完成mysql 已创建表中某字段的修改