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

发表于 | 更新于 | 分类于 |
热度 ℃   |   字数统计: 2,607(字)   |   阅读时长: 13(分) |

前言

提前说明这是一篇小白总结，高手勿喷请绕行，写这篇总结的原因是发觉自己有时候确实眼高手低了，大道至简，花了很多时间去看索引、缓存、主从等等，等到出现实际问题的时候却发现自己磨磨蹭蹭写出的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;

这个语句中的TO和AS是可选的，加不加都行，也可以选择其中一个，效果是一样的，测试如下：

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
欢迎您扫一扫上面的微信公众号，订阅我的博客