首页 > 资讯 > 数据库 >MySQL导入.sql文件方法以及导入失败的问题解决

339

分享到

MySQL导入.sql文件方法以及导入失败的问题解决

sql mysql 数据库 2023-09-02 18:09:57 339人浏览安东尼

摘要

首先这是聂老师的sql文件 -- MySQL dump 10.13 Distrib 8.0.27, for Win64 (x86_64)---- Host: localhost Database: teaching-- ------

首先这是聂老师的sql文件

-- MySQL dump 10.13  Distrib 8.0.27, for Win64 (x86_64)---- Host: localhost    Database: teaching-- -------------------------------------------------------- Server version8.0.27;;;;;;;;;;---- Table structure for table `advisor`--DROP TABLE IF EXISTS `advisor`;;;CREATE TABLE `advisor` (  `s_ID` varchar(5) NOT NULL,  `i_ID` varchar(5) DEFAULT NULL,  PRIMARY KEY (`s_ID`),  KEY `i_ID` (`i_ID`),  CONSTRAINT `advisor_ibfk_1` FOREIGN KEY (`i_ID`) REFERENCES `instructor` (`ID`) ON DELETE SET NULL,  CONSTRAINT `advisor_ibfk_2` FOREIGN KEY (`s_ID`) REFERENCES `student` (`ID`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `advisor`--LOCK TABLES `advisor` WRITE;;INSERT INTO `advisor` VALUES ('12345','10101'),('44553','22222'),('45678','22222'),('00128','45565'),('76543','45565'),('23121','76543'),('98988','76766'),('76653','98345'),('98765','98345');;UNLOCK TABLES;---- Table structure for table `classroom`--DROP TABLE IF EXISTS `classroom`;;;CREATE TABLE `classroom` (  `building` varchar(15) NOT NULL,  `room_number` varchar(7) NOT NULL,  `capacity` decimal(4,0) DEFAULT NULL,  PRIMARY KEY (`building`,`room_number`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `classroom`--LOCK TABLES `classroom` WRITE;;INSERT INTO `classroom` VALUES ('Packard','101',500),('Painter','514',10),('Taylor','3128',70),('Watson','100',30),('Watson','120',50);;UNLOCK TABLES;---- Table structure for table `course`--DROP TABLE IF EXISTS `course`;;;CREATE TABLE `course` (  `course_id` varchar(8) NOT NULL,  `title` varchar(50) DEFAULT NULL,  `dept_name` varchar(20) DEFAULT NULL,  `credits` decimal(2,0) DEFAULT NULL,  PRIMARY KEY (`course_id`),  KEY `dept_name` (`dept_name`),  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,  CONSTRAINT `course_chk_1` CHECK ((`credits` > 0))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `course`--LOCK TABLES `course` WRITE;;INSERT INTO `course` VALUES ('BIO-101','Intro. to Biology','Biology',4),('BIO-301','Genetics','Biology',4),('BIO-399','Computational Biology','Biology',3),('CS-101','Intro. to Computer Science','Comp. Sci.',4),('CS-190','Game Design','Comp. Sci.',4),('CS-315','Robotics','Comp. Sci.',3),('CS-319','Image Processing','Comp. Sci.',3),('CS-347','Database System Concepts','Comp. Sci.',3),('EE-181','Intro. to Digital Systems','Elec. Eng.',3),('FIN-201','Investment Banking','Finance',3),('HIS-351','World History','History',3),('MU-199','Music Video Production','Music',3),('PHY-101','Physical Principles','Physics',4);;UNLOCK TABLES;---- Table structure for table `department`--DROP TABLE IF EXISTS `department`;;;CREATE TABLE `department` (  `dept_name` varchar(20) NOT NULL,  `building` varchar(15) DEFAULT NULL,  `budget` decimal(12,2) DEFAULT NULL,  PRIMARY KEY (`dept_name`),  CONSTRAINT `department_chk_1` CHECK ((`budget` > 0))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `department`--LOCK TABLES `department` WRITE;;INSERT INTO `department` VALUES ('Biology','Watson',90000.00),('Comp. Sci.','Taylor',100000.00),('Elec. Eng.','Taylor',85000.00),('Finance','Painter',120000.00),('History','Painter',50000.00),('Music','Packard',80000.00),('Physics','Watson',70000.00);;UNLOCK TABLES;---- Table structure for table `instructor`--DROP TABLE IF EXISTS `instructor`;;;CREATE TABLE `instructor` (  `ID` varchar(5) NOT NULL,  `name` varchar(20) NOT NULL,  `dept_name` varchar(20) DEFAULT NULL,  `salary` decimal(8,2) DEFAULT NULL,  PRIMARY KEY (`ID`),  KEY `dept_name` (`dept_name`),  CONSTRAINT `instructor_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,  CONSTRAINT `instructor_chk_1` CHECK ((`salary` > 29000))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `instructor`--LOCK TABLES `instructor` WRITE;;INSERT INTO `instructor` VALUES ('10101','Srinivasan','Comp. Sci.',65000.00),('12121','Wu','Finance',90000.00),('15151','Mozart','Music',40000.00),('22222','Einstein','Physics',95000.00),('32343','El Said','History',60000.00),('33456','Gold','Physics',87000.00),('45565','Katz','Comp. Sci.',75000.00),('58583','Califieri','History',62000.00),('76543','Singh','Finance',80000.00),('76766','Crick','Biology',72000.00),('83821','Brandt','Comp. Sci.',92000.00),('98345','Kim','Elec. Eng.',80000.00);;UNLOCK TABLES;---- Table structure for table `prereq`--DROP TABLE IF EXISTS `prereq`;;;CREATE TABLE `prereq` (  `course_id` varchar(8) NOT NULL,  `prereq_id` varchar(8) NOT NULL,  PRIMARY KEY (`course_id`,`prereq_id`),  KEY `prereq_id` (`prereq_id`),  CONSTRAINT `prereq_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE,  CONSTRAINT `prereq_ibfk_2` FOREIGN KEY (`prereq_id`) REFERENCES `course` (`course_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `prereq`--LOCK TABLES `prereq` WRITE;;INSERT INTO `prereq` VALUES ('BIO-301','BIO-101'),('BIO-399','BIO-101'),('CS-190','CS-101'),('CS-315','CS-101'),('CS-319','CS-101'),('CS-347','CS-101'),('EE-181','PHY-101');;UNLOCK TABLES;---- Table structure for table `section`--DROP TABLE IF EXISTS `section`;;;CREATE TABLE `section` (  `course_id` varchar(8) NOT NULL,  `sec_id` varchar(8) NOT NULL,  `semester` varchar(6) NOT NULL,  `year` decimal(4,0) NOT NULL,  `building` varchar(15) DEFAULT NULL,  `room_number` varchar(7) DEFAULT NULL,  `time_slot_id` varchar(4) DEFAULT NULL,  PRIMARY KEY (`course_id`,`sec_id`,`semester`,`year`),  KEY `building` (`building`,`room_number`),  CONSTRAINT `section_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE,  CONSTRAINT `section_ibfk_2` FOREIGN KEY (`building`, `room_number`) REFERENCES `classroom` (`building`, `room_number`) ON DELETE SET NULL,  CONSTRAINT `section_chk_1` CHECK ((`semester` in (_gbk'Fall',_gbk'Winter',_gbk'Spring',_gbk'Summer'))),  CONSTRAINT `section_chk_2` CHECK (((`year` > 1701) and (`year` < 2100)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `section`--LOCK TABLES `section` WRITE;;INSERT INTO `section` VALUES ('BIO-101','1','Summer',2017,'Painter','514','B'),('BIO-301','1','Summer',2018,'Painter','514','A'),('CS-101','1','Fall',2017,'Packard','101','H'),('CS-101','1','Spring',2018,'Packard','101','F'),('CS-190','1','Spring',2017,'Taylor','3128','E'),('CS-190','2','Spring',2017,'Taylor','3128','A'),('CS-315','1','Spring',2018,'Watson','120','D'),('CS-319','1','Spring',2018,'Watson','100','B'),('CS-319','2','Spring',2018,'Taylor','3128','C'),('CS-347','1','Fall',2017,'Taylor','3128','A'),('EE-181','1','Spring',2017,'Taylor','3128','C'),('FIN-201','1','Spring',2018,'Packard','101','B'),('HIS-351','1','Spring',2018,'Painter','514','C'),('MU-199','1','Spring',2018,'Packard','101','D'),('PHY-101','1','Fall',2017,'Watson','100','A');;UNLOCK TABLES;---- Table structure for table `student`--DROP TABLE IF EXISTS `student`;;;CREATE TABLE `student` (  `ID` varchar(5) NOT NULL,  `name` varchar(20) NOT NULL,  `dept_name` varchar(20) DEFAULT NULL,  `tot_cred` decimal(3,0) DEFAULT NULL,  PRIMARY KEY (`ID`),  KEY `dept_name` (`dept_name`),  CONSTRAINT `student_ibfk_1` FOREIGN KEY (`dept_name`) REFERENCES `department` (`dept_name`) ON DELETE SET NULL,  CONSTRAINT `student_chk_1` CHECK ((`tot_cred` >= 0))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `student`--LOCK TABLES `student` WRITE;;INSERT INTO `student` VALUES ('00128','Zhang','Comp. Sci.',102),('12345','Shankar','Comp. Sci.',32),('19991','Brandt','History',80),('23121','Chavez','Finance',110),('44553','Peltier','Physics',56),('45678','Levy','Physics',46),('54321','Williams','Comp. Sci.',54),('55739','Sanchez','Music',38),('70557','Snow','Physics',0),('76543','Brown','Comp. Sci.',58),('76653','Aoi','Elec. Eng.',60),('98765','Bourikas','Elec. Eng.',98),('98988','Tanaka','Biology',120);;UNLOCK TABLES;---- Table structure for table `takes`--DROP TABLE IF EXISTS `takes`;;;CREATE TABLE `takes` (  `ID` varchar(5) NOT NULL,  `course_id` varchar(8) NOT NULL,  `sec_id` varchar(8) NOT NULL,  `semester` varchar(6) NOT NULL,  `year` decimal(4,0) NOT NULL,  `grade` varchar(2) DEFAULT NULL,  PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),  KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),  CONSTRAINT `takes_ibfk_1` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE,  CONSTRAINT `takes_ibfk_2` FOREIGN KEY (`ID`) REFERENCES `student` (`ID`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `takes`--LOCK TABLES `takes` WRITE;;INSERT INTO `takes` VALUES ('00128','CS-101','1','Fall',2017,'A'),('00128','CS-347','1','Fall',2017,'A-'),('12345','CS-101','1','Fall',2017,'C'),('12345','CS-190','2','Spring',2017,'A'),('12345','CS-315','1','Spring',2018,'A'),('12345','CS-347','1','Fall',2017,'A'),('19991','HIS-351','1','Spring',2018,'B'),('23121','FIN-201','1','Spring',2018,'C+'),('44553','PHY-101','1','Fall',2017,'B-'),('45678','CS-101','1','Fall',2017,'F'),('45678','CS-101','1','Spring',2018,'B+'),('45678','CS-319','1','Spring',2018,'B'),('54321','CS-101','1','Fall',2017,'A-'),('54321','CS-190','2','Spring',2017,'B+'),('55739','MU-199','1','Spring',2018,'A-'),('76543','CS-101','1','Fall',2017,'A'),('76543','CS-319','2','Spring',2018,'A'),('76653','EE-181','1','Spring',2017,'C'),('98765','CS-101','1','Fall',2017,'C-'),('98765','CS-315','1','Spring',2018,'B'),('98988','BIO-101','1','Summer',2017,'A'),('98988','BIO-301','1','Summer',2018,NULL);;UNLOCK TABLES;---- Table structure for table `teaches`--DROP TABLE IF EXISTS `teaches`;;;CREATE TABLE `teaches` (  `ID` varchar(5) NOT NULL,  `course_id` varchar(8) NOT NULL,  `sec_id` varchar(8) NOT NULL,  `semester` varchar(6) NOT NULL,  `year` decimal(4,0) NOT NULL,  PRIMARY KEY (`ID`,`course_id`,`sec_id`,`semester`,`year`),  KEY `course_id` (`course_id`,`sec_id`,`semester`,`year`),  CONSTRAINT `teaches_ibfk_1` FOREIGN KEY (`course_id`, `sec_id`, `semester`, `year`) REFERENCES `section` (`course_id`, `sec_id`, `semester`, `year`) ON DELETE CASCADE,  CONSTRAINT `teaches_ibfk_2` FOREIGN KEY (`ID`) REFERENCES `instructor` (`ID`) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `teaches`--LOCK TABLES `teaches` WRITE;;INSERT INTO `teaches` VALUES ('76766','BIO-101','1','Summer',2017),('76766','BIO-301','1','Summer',2018),('10101','CS-101','1','Fall',2017),('45565','CS-101','1','Spring',2018),('83821','CS-190','1','Spring',2017),('83821','CS-190','2','Spring',2017),('10101','CS-315','1','Spring',2018),('45565','CS-319','1','Spring',2018),('83821','CS-319','2','Spring',2018),('10101','CS-347','1','Fall',2017),('98345','EE-181','1','Spring',2017),('12121','FIN-201','1','Spring',2018),('32343','HIS-351','1','Spring',2018),('15151','MU-199','1','Spring',2018),('22222','PHY-101','1','Fall',2017);;UNLOCK TABLES;---- Table structure for table `time_slot`--DROP TABLE IF EXISTS `time_slot`;;;CREATE TABLE `time_slot` (  `time_slot_id` varchar(4) NOT NULL,  `day` varchar(1) NOT NULL,  `start_hr` decimal(2,0) NOT NULL,  `start_min` decimal(2,0) NOT NULL,  `end_hr` decimal(2,0) DEFAULT NULL,  `end_min` decimal(2,0) DEFAULT NULL,  PRIMARY KEY (`time_slot_id`,`day`,`start_hr`,`start_min`),  CONSTRAINT `time_slot_chk_1` CHECK (((`start_hr` >= 0) and (`start_hr` < 24))),  CONSTRAINT `time_slot_chk_2` CHECK (((`start_min` >= 0) and (`start_min` < 60))),  CONSTRAINT `time_slot_chk_3` CHECK (((`end_hr` >= 0) and (`end_hr` < 24))),  CONSTRAINT `time_slot_chk_4` CHECK (((`end_min` >= 0) and (`end_min` < 60)))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;;---- Dumping data for table `time_slot`--LOCK TABLES `time_slot` WRITE;;INSERT INTO `time_slot` VALUES ('A','F',8,0,8,50),('A','M',8,0,8,50),('A','W',8,0,8,50),('B','F',9,0,9,50),('B','M',9,0,9,50),('B','W',9,0,9,50),('C','F',11,0,11,50),('C','M',11,0,11,50),('C','W',11,0,11,50),('D','F',13,0,13,50),('D','M',13,0,13,50),('D','W',13,0,13,50),('E','R',10,30,11,45),('E','T',10,30,11,45),('F','R',14,30,15,45),('F','T',14,30,15,45),('G','F',16,0,16,50),('G','M',16,0,16,50),('G','W',16,0,16,50),('H','W',10,0,12,30);;UNLOCK TABLES;;;;;;;;;-- Dump completed on 2022-08-29 14:09:25

然后我们现在有很多种导入方法

导入sql文件的几种方法

1.使用工具Navicat for Mysql导入

该工具下载路径:Navicat

1.1打开localhost_3306,选中右击“新建数据库”

1.2指定数据库名和字符集（可根据sql文件的字符集类型自行选择）

1.3选中数据库下的表运行SQL文件

1.4选中路径导入

文件路径可以和上图不一样,自己设定就好了

2.使用官方工具mysql Workbench导入

2.1NO.1

新建一个数据库,名字任取,这里建议取和你sql文件相同的名称,然后点击指示图标(或者File栏里面的Open SQL Script...)

选中路径后导入SQL文件
添加指定库名的命令.并点击运行

PS：不用点保存，导入后直接×掉即可，保存会修改sql文件本身；

在左侧栏点击刷新查看结果

2.2NO.2

点击导入(或者Server栏里面的Data Import)
选择导入文件的路径
Start Import
刷新查看结果

3.使用命令行导入

点击开始菜单输入cmd(或者按住win+R)回车,打开dos界面

输入"mysql -u root -p",然后输入数据库密码
create database teaching; 新建一个数据库
选中数据库 use teaching;
选中导入的路径"source D:/teaching.sql;"
查看表show tables;

导入sql文件时遇见的一些问题

1.MySQL中创建外键的错误：1215 Cannot add the foreign key constraint

引言： MySQL中经常会需要创建父子表之间的约束，这个约束是需要建立在主外键基础之上的，这里解决了一个在创建主外键约束过程中碰到的一个问题。

1. 问题的提出

创建两个表：

product: 商品表

sealer: 供货商表

相应的ＳＱＬ如下：

　　product:DROP TABLE IF EXISTS `product`;CREATE TABLE `product` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(20) NOT NULL COMMENT 'product name',  `price` float(10,3) NOT NULL,  `description` varchar(20) DEFAULT NULL,  `count` int(11) NOT NULL DEFAULT '0',  `sid` int(11) NOT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `id_index` (`id`) USING HASH,  UNIQUE KEY `sid_index` (`sid`) USING HASH) ENGINE=InnoDB DEFAULT CHARSET=utf8;   sealer:DROP TABLE IF EXISTS `sealer`;CREATE TABLE `sealer` (  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,  `name` varchar(30) NOT NULL,  `city` varchar(255) DEFAULT NULL,  `created_time` datetime DEFAULT NULL,  `updated_time` datetime DEFAULT NULL,  `level` int(11) NOT NULL DEFAULT '0',  `description` varchar(40) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `id_index_1` (`id`) USING HASH) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

接下来我们需要关联product.sid 至 sealer.id，进行父子表的主外键关联。

2. 碰到错误

在创建外键之时，使用的SQL和碰到错误信息如下:

alter table `product' add CONSTRAINT `sid_ref` FOREIGN KEY (`sid`) REFERENCES `sealer` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION

碰到的错误信息如下：

无法正确的插入外键约束。

3. 问题分析

主外键更多的是某表的主键与子表的某个列进行关联，要求是具备相同的数据类型和属性，问题会不会出现在这里？

要求：具备相同的数据类型和约束

发现： unsigned，数字的字符长度不一致。

4. 解决的办法

修改product.sid中的数据类型，添加unsigned和字段的长度，将其设置为相同即可

5. 总结

之所以出现1215的问题，是由于主外键之间的数据类型不一致造成的，以后类似问题，皆可按此处理。

2.聂老师sql文件版本问题

大家在导入的时候会发现,出现了很多莫名其妙的报错,然后导致很多表没有成功导进去,我们在每一个表的sql语句上找共同点,会发现,都会有字符集的解释作为结尾,但是这里的字符集,大部分人的5.6版本是不支持的,所以要么卸载所有的现MySQL版本,要么就修改聂老师的teaching.sql文件,这两个方法都非常复杂.

第一种还好,虽然卸载程序比较繁琐,但是只要跟着网上的教程一步一步来,最后把注册表删删干净就没多大问题.

第二种是真的让我破防了.

首先我在MySQL Workbench里面打开了teaching.sql文件,试着运行过去,好不容易发现并修改了字符集的错误,然后又莫名其妙地出现了无法创建外键连接的错误.

我寻找了网上的教程,如上所说,导致外键创建失败的原因主要有以下两点:

外键字段不能为该表的主键
外键字段参考字段必须为参考表的主键.

如果出现”cannot add foreign key constraint”的情况，建议先检查一下，主表是否有主键以及从表关联字段是否为从表主键，然后，核对从表字段与主表关联字段的数据类型是否相同，其中是否有不符合的数据(不好弄就先把数据备份，然后删完了试一下，如果可以，说明确实是存在不符合的数据问题)。

为此我还去问了老师.他的回答也是如此.