登录
首页 >  数据库 >  MySQL

MySql迁移数据以及删除某字段爬坑记

来源:SegmentFault

时间:2023-02-16 15:26:48 454浏览 收藏

哈喽!今天心血来潮给大家带来了《MySql迁移数据以及删除某字段爬坑记》,想必大家应该对数据库都不陌生吧,那么阅读本文就都不会很困难,以下内容主要涉及到MySQL,若是你正在学习数据库,千万别错过这篇文章~希望能帮助到你!

建表

在SpringBoot中,jpa可以自动生成数据表,前提是要把字段定义好

    /**
     * 学院
     */
    @ManyToMany(cascade = {CascadeType.REMOVE})
    @JoinTable(name = "user_colleges",
            joinColumns = @JoinColumn(name = "users_id"),
            inverseJoinColumns = @JoinColumn(name = "colleges_id")
    )
    @JsonView(CollegeJsonView.class)
    private List colleges = new ArrayList();

cascade = {CascadeType.REMOVE} 表示该实体拥有对中间表数据进行删除操作的权限, @JoinTable里面各属性用以定义中间表名称,字段名称等。
然后运行项目,可以发现中间表生成了:

image.png

数据迁移

由于没接触过太多SQL语句,所以只能求助谷歌,好在网上类似的需求很多,我们要将user表里的 id, college_id 两个字段迁移到user_colleges表里对应的 users_id, colleges_id 两个字段,对应的语句如下:

INSERT INTO user_colleges (users_id, colleges_id) SELECT id, college_id FROM user;

上面语句的意思是,在user表里遍历id, college_id 字段的数据并插入到user_colleges表的对应字段,语句写好了,然后执行命令,果然,报错了:

914CB415978FD2E2CC12833C2E1EDBD2.jpg

字段colleges_id不能为空,然后看一下为啥会报错,由于是从user表里迁移,打开user表看一下,竟然有的数据是空的:
B3A7402E0955E61C4F548FBDEC3BE887.jpg

只能更改条件,既然有的是空的,那就以是否为空作为判断条件:

INSERT INTO user_colleges (users_id, colleges_id) SELECT id, college_id FROM user WHERE college_id IS NOT NULL ;

查找不是空的数据插入到相应的表中,执行:

1217A673B7878C19525B46AB24687B69.jpg

数据成功迁移,然后再迁移是空的数据:

INSERT INTO user_colleges (users_id) SELECT id FROM user WHERE college_id IS NULL ;

出人意料的是,报错了竟然:

1815212A-F922-4FB5-A118-F854C7E51417.png

由于我们只迁移了user字段,colleges_id字段又没有默认值,所以迁移失败,我们只能用SQL语句设置字段默认值:

ALTER TABLE user_colleges  MODIFY colleges_id bigint(20) NULL;

要注意的是,要把字段类型标注清楚,然后运行,数据成功导入:

C6DDCD8C777D73DD6B01EE2F49A2F8A8.jpg

删除字段

之前进行了数据表数据的迁移,那么user表里的college_id字段也就没用了,那么就要用SQL语句删除:

ALTER TABLE user DROP COLUMN college_id;

直接从user表删除college_id字段,但是运行的时候又报错了:

0CDA1FE858EDF601FC5927E76342C15D.jpg

Cannot drop index 'FKhjwatvca6uub9ctj1pwkgosm5': needed in a foreign key constraint
无法删除索引'FKhjwatvca6uub9ctj1pwkgosm5':在外键约束中需要

由于college_id是外键,先要解除外键约束,才能删除这个字段,先用一条语句看一下外键KEY值:

SHOW CREATE TABLE user

然后我们就能看到表的一些信息了:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `college_id` bigint(20) DEFAULT NULL,
  `certificate_number` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `job_number` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `sex` bit(1) DEFAULT NULL,
  `delete_at` bigint(20) DEFAULT '0',
  `is_admin` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK5bd9yqnpf0w0teyr7iw28u388` (`username`,`delete_at`),
  KEY `FKhjwatvca6uub9ctj1pwkgosm5` (`college_id`),
  CONSTRAINT `FKhjwatvca6uub9ctj1pwkgosm5` FOREIGN KEY (`college_id`) REFERENCES `college` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4

删除外键即可:

ALTER TABLE user DROP FOREIGN KEY FKhjwatvca6uub9ctj1pwkgosm5;

删除外键之后,我们就可以删除被外键约束的字段了:

ALTER TABLE user DROP COLUMN college_id;

然后再看一下表单信息,字段成功删除:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8 NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 NOT NULL,
  `certificate_number` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `job_number` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `sex` bit(1) DEFAULT NULL,
  `delete_at` bigint(20) DEFAULT '0',
  `is_admin` bit(1) DEFAULT b'0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK5bd9yqnpf0w0teyr7iw28u388` (`username`,`delete_at`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4

总结

以前一直以为对数据库操作一行行语句就能解决了,但是深入接触之后才发现要考虑很多因素,NULL值,默认值,字段类型等,不过一步步探索下来,发现真的特别有趣,这就是计算机的魅力吧。

本文作者:河北工业大学梦云智开发团队 张文达

到这里,我们也就讲完了《MySql迁移数据以及删除某字段爬坑记》的内容了。个人认为,基础知识的学习和巩固,是为了更好的将其运用到项目中,欢迎关注golang学习网公众号,带你了解更多关于mysql的知识点!

声明:本文转载于:SegmentFault 如有侵犯,请联系study_golang@163.com删除
相关阅读
更多>
最新阅读
更多>
课程推荐
更多>
评论列表