front mysql 导出表结构_mysql导出表结构

使用mysql的Mysqldump导出和导入表结构

-- 命令如下：

mysqldump -d -h localhost -u root -pmypassword databasename > dumpfile.sql

-- 其中-d 表示只导出表结构，不导出数据，-h 表示host，-u表示用户，-p表示密码 database表示导出那个数据库 ，dumpfile.sql 表示导出生成的sql语句

--有以下数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sakila |

| test |

| world |

+--------------------+

6 rows in set (0.00 sec)

mysql>

-- 例如，导出test数据库下的表，test数据库下有以下的表ld_csv1、t1

mysql> use test

Database changed

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| ld_csv1 |

| t1 |

+----------------+

2 rows in set (0.00 sec)

mysql>

mysql> desc t1;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | varchar(2) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql>

-- 例如，导出test数据库下的表，

mysqldump -d -h 127.0.0.1 -uroot -pmysql test > d:\dumpfile.sql

-- 生成的dumpfile.sql内容如下：

-- MySQL dump 10.13 Distrib 5.6.40, for Win64 (x86_64)

-- Host: 127.0.0.1 Database: test

-- Server version 5.6.40-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 ld_csv1

--

DROP TABLE IF EXISTS ld_csv1;

/*!40101 SET @saved_cs_client = @@character_set_client /;

/!40101 SET character_set_client = utf8 /;

CREATE TABLE ld_csv1 (

id int(11) NOT NULL DEFAULT '0',

username varchar(5) NOT NULL,

city varchar(6) NOT NULL,

email varchar(50) NOT NULL

) ENGINE=CSV DEFAULT CHARSET=gbk;

/!40101 SET character_set_client = @saved_cs_client */;

--

-- Table structure for table t1

--

DROP TABLE IF EXISTS t1;

/*!40101 SET @saved_cs_client = @@character_set_client /;

/!40101 SET character_set_client = utf8 /;

CREATE TABLE t1 (

id varchar(2) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/!40101 SET character_set_client = @saved_cs_client /;

/!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 2018-09-20 11:04:07

-- 将导出的表结构文件dumpfile.sql复制到要导入的库所在的服务器，进行导入。将表结构导入到world数据库中，

-- world数据库，里面有t1表，t1表有一条记录

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;

+------+------+

| id | name |

+------+------+

| a | b |

+------+------+

1 row in set (0.00 sec)

mysql> select database();

+------------+

| database() |

+------------+

| world |

+------------+

1 row in set (0.00 sec)

mysql>

-- 进行导入 ，在mysql上执行source d:\dumpfile.sql

mysql> source d:\dumpfile.sql

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.16 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>

-- 导入后，world库里面有t1表和ld_csv1。 并且t1表的表结构被替换(原来原来world库上的t1表有id和name字段，导入后只有id字段)

mysql> show tables;

+-----------------+

| Tables_in_world |

+-----------------+

| city |

| country |

| countrylanguage |

| ld_csv1 |

| t1 |

+-----------------+

5 rows in set (0.00 sec)

mysql> desc t1

-> ;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | varchar(2) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

mysql>

END