MySQL数据库导入、导出、复制表、重命名表

前言

提前说明这是一篇小白总结,高手勿喷请绕行,写这篇总结的原因是发觉自己有时候确实眼高手低了,大道至简,花了很多时间去看索引、缓存、主从等等,等到出现实际问题的时候却发现自己磨磨蹭蹭写出的SQL语句居然有语法错误,看来还得稳扎稳打从基础入手,因为实际工作的用到的SQL并不多,现在把常用的几条总结一下,即使下次不能立马写出来,也能在这篇文章中的快速找到想要的。

正如标题中的提到的这些,数据库的导入和导出在紧急处理线上数据时很常用,而复制表基本上也是为了不影响原数据的情况下进行问题排查,重命名表是为了导入多份备份数据时原数据不被覆盖,比如想对比两天的A表数据,可以先把第一天的数据导入,然后将A表名修改成Aold,接着直接再导入第二天的数据库数据,这样就可以将数据库中表Aold和A进行对比了,可以避免两个数据库中的同一个表进行对比时写很长的SQL。

测试环境

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

测试过程

为了说明实现这些要求的具体SQL,我们先建立一个测试数据库,然后创建测试表格,插入测试数据,最后在这个数据库上依次实现这些要求。

创建测试数据

创建测试数据库和表格

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> create database dbtest;
Query OK, 1 row affected (0.00 sec)

mysql> use dbtest
Database changed

mysql> create table a(id int, num int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table b(id int, name varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+--------------+
| Tables_in_zz |
+--------------+
| a |
| b |
+--------------+
2 rows in set (0.00 sec)

插入测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> insert into a values(1, 100);
Query OK, 1 row affected (0.02 sec)

mysql> insert into a values(2, 200);
Query OK, 1 row affected (0.01 sec)

mysql> select * from a;
+------+------+
| id | num |
+------+------+
| 1 | 100 |
| 2 | 200 |
+------+------+
2 rows in set (0.01 sec)

mysql> insert into b values(1, 'albert');
Query OK, 1 row affected (0.01 sec)

mysql> insert into b values(2, 'tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from b;
+------+--------+
| id | name |
+------+--------+
| 1 | albert |
| 2 | tom |
+------+--------+
2 rows in set (0.00 sec)

数据库导出

数据库导出时使用的最基础的工具叫mysqldump,这是单独的工具不是mysql命令,刚学MySQL的时候居然在MySQL的命令行中使用mysqldump,现在只能当笑话看了。

导出指定数据库中所有表结构和数据

在系统的命令行工具下输入以下命令,敲入回车输入密码,再回车就可以将数据库dbtest的结构和数据导出到dbtest.sql文件中:

1
>mysqldump -uroot -h192.168.1.101 -p dbtest > dbtest.sql

打开dbtest.sql文件,显示如下:文件内容比较长,里面包含了数据库的表结构和其中的数据信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- MySQL dump 10.13  Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost Database: dbtest
-- ------------------------------------------------------
-- Server version 5.7.21-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `a`
--

DROP TABLE IF EXISTS `a`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `a` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `a`
--

LOCK TABLES `a` WRITE;
/*!40000 ALTER TABLE `a` DISABLE KEYS */;
INSERT INTO `a` VALUES (1,100),(2,200);
/*!40000 ALTER TABLE `a` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `b`
--

DROP TABLE IF EXISTS `b`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `b` (
`id` int(11) DEFAULT NULL,
`name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `b`
--

LOCK TABLES `b` WRITE;
/*!40000 ALTER TABLE `b` DISABLE KEYS */;
INSERT INTO `b` VALUES (1,'albert'),(2,'tom');
/*!40000 ALTER TABLE `b` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-11-30 11:32:23

只导出指定数据库中所有表的结构

只导出表结构的方法和上面是一样的,只是加上 -d 选项就可以了,运行下面命令就可以将dbtest数据库中的所有表结构导出到 dbteststructure.sql 中,因为和上面类似,文件中的内容就不贴了,只比 dbtest.sql 文件少了插入数据的内容:

1
>mysqldump -uroot -h192.168.1.101 -p -d dbtest > dbteststructure.sql

只导出指定数据库中的一个表

只导出数据库中指定表,可以是一个也可以是多个,在数据库名字后面跟表的名字就可以了,比如导出表a:

1
>mysqldump -uroot -h192.168.1.101 -p dbtest a > dbtest_a.sql

导出多个数据库数据

出多个数据库数据需要加上 --databases 选项,然后在后面依次跟上数据库名字就行:

1
>mysqldump -uroot -h192.168.1.101 -p --databases dbtest dbtest2 > db_more.sql

导出所有数据库数据

导出所有的数据库时不需要加数据库的名字,加上 --all-databases 选项就可以了

1
>mysqldump -uroot -h192.168.1.101 -p --all-databases > db_all.sql

数据库导入

数据库的导入比较简单,实际上就是把sql文件在MySQL中执行一下,可以使用以下两种方式:

系统命令行导入

一般需要指定导入的数据库dbtest和sql文件的路径,在Linux上举例:

1
>mysql -uroot -h192.168.1.101 -p dbtest < /home/albert/dbtest.sql --default-character-set=utf8

在Windows上举例,主要是路径需要注意,Windows上使用正斜杠/和反斜杠\都可以,默认是反斜杠,如果路径中包含空格可以用双引号将整个路径包起来:

1
>mysql -uroot -h192.168.1.101 -p dbtest < D:\albert\dbtest.sql --default-character-set=utf8

注意--default-character-set=utf8是指定默认的字符集,主要是防止导入时出现编码错误,之前总结过,在此复习一下。

MySQL命令行导入

首先连接MySQL服务器进行登陆:

1
>mysql -uroot -h192.168.1.101 -p --default-character-set=utf8

输入密码登陆后再使用source命令直接导入sql文件就可以:

1
mysql> source D:\albert\dbtest.sql

数据表复制

数据表的复制可以分为结构复制和完全复制,其中完全复制时可以先复制结构,再将数据复制到新表中:

只复制表结构

  • 使用LIKE语句,只不过5.0版本之后才支持,之前的版本无法使用
1
CREATE TABLE new_table LIKE old_table;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from a;
+------+------+
| id | num |
+------+------+
| 1 | 100 |
| 2 | 200 |
+------+------+
2 rows in set (0.01 sec)

mysql> create table a2 like a;
Query OK, 0 rows affected (0.04 sec)

mysql> desc a2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)

mysql> select * from a2;
Empty set (0.00 sec)
  • 使用 SELECT 语句加不成立的条件实现
1
CREATE TABLE new_table SELECT * FROM old_table WHERE FALSE;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> create table a3 select * from a where false;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc a3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from a3;
Empty set (0.01 sec)

复制表结构和数据

  • 可以先按照上面的语句复制结构,然后再讲数据复制过去:
1
2
CREATE TABLE new_table SELECT * FROM old_table WHERE FALSE;
INSERT INTO new_table SELECT * FROM old_table;
1
2
3
4
5
6
7
8
9
10
11
12
mysql> insert into a2 select * from a;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from a2;
+------+------+
| id | num |
+------+------+
| 1 | 100 |
| 2 | 200 |
+------+------+
2 rows in set (0.00 sec)
  • 直接将结构和数据全部复制
1
CREATE TABLE new_table SELECT * FROM old_table;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> create table a4 select * from a;
Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> desc a4;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> select * from a4;
+------+------+
| id | num |
+------+------+
| 1 | 100 |
| 2 | 200 |
+------+------+
2 rows in set (0.00 sec)

数据表重命名

使用 ALTER 命令实现

1
ALTER TABLE old_table RENAME [TO|AS] new_table;

这个语句中的TOAS是可选的,加不加都行,也可以选择其中一个,效果是一样的,测试如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| b |
+------------------+
5 rows in set (0.02 sec)

mysql> alter table b rename c;
Query OK, 0 rows affected (0.04 sec)

mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| c |
+------------------+
5 rows in set (0.00 sec)

mysql> alter table c rename to d;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| d |
+------------------+
5 rows in set (0.00 sec)

mysql> alter table d rename as e;
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| e |
+------------------+
5 rows in set (0.00 sec)

使用RENAME命令

1
RENAME TABLE old_table TO new_table;

这个语句中TO就不能省略了,否则会报语法错误,测试如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show tables
-> ;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| e |
+------------------+
5 rows in set (0.00 sec)

mysql> rename table e to f;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
+------------------+
| Tables_in_dbtest |
+------------------+
| a |
| f |
+------------------+
5 rows in set (0.01 sec)

总结

  1. 数据库的导出、导入、数据表的复制、重命名都是MySQL操作的基础,需要熟练掌握
  2. 数据库导出:mysqldump -uroot -h192.168.1.101 -p dbtest > dbtest.sql
  3. 数据库导入:mysql -uroot -h192.168.1.101 -p dbtest < /tmp/dbtest.sql --default-character-set=utf8
  4. 数据表复制:CREATE TABLE new_table SELECT * FROM old_table;
  5. 表格重命名:RENAME TABLE old_table TO new_table;
Albert Shi wechat
欢迎您扫一扫上面的微信公众号,订阅我的博客