如何轻松地将多个 sql 文件导入 MySQL 数据库？

How to easily import multiple sql files into a MySQL database?

提问by Chococroc

I have several sqlfiles and I want to importall of them at once into a MySQL database.

我有几个sql文件，我想一次将它们全部导入到 MySQL 数据库中。

I go to PHPMyAdmin, access the database, click import, select a file and import it. When I have more than a couple of files it takes a long time.

我转到PHPMyAdmin，访问数据库，单击import，选择一个文件并导入它。当我有多个文件时，它需要很长时间。

I would like to know if there is a better way to import multiple files, something like one file which will import the other files or similar.

我想知道是否有更好的方法来导入多个文件，例如一个文件将导入其他文件或类似文件。

I'm using WAMPand I would like a solution that does not require installing additional programson my computer.

我正在使用WAMP并且我想要一个不需要在我的计算机上安装其他程序的解决方案。

回答by Chococroc

In Windows, open a terminal, go to the content folder and write:

在Windows 中，打开终端，转到内容文件夹并写入：

copy /b *.sql all_files.sql

This concate all files in only one, making it really quick to import with PhpMyAdmin.

这将所有文件合并为一个，从而可以非常快速地使用 PhpMyAdmin 进行导入。

In Linuxand macOS, as @BlackCharly pointed out, this will do the trick:

在Linux和macOS 中，正如@BlackCharly 所指出的，这可以解决问题：

cat *.sql  > .all_files.sql

Important Note: Doing it directly should go well, but it could end up with you stuck in a loop with a massive output file getting bigger and bigger due to the system adding the file to itself. To avoid it, two possible solutions.

重要说明：直接执行应该会顺利，但最终可能会导致您陷入循环，由于系统将文件添加到自身，大量输出文件变得越来越大。为了避免它，两种可能的解决方案。

A) Put the result in a separate directory to be safe (Thanks @mosh):

A) 将结果放在一个单独的目录中以确保安全（谢谢@mosh）：

mkdir concatSql
cat *.sql  > ./concatSql/all_files.sql

B) Concat them in a file with a different extension and then change it the name. (Thanks @William Turrell)

B）将它们连接到具有不同扩展名的文件中，然后更改名称。（感谢@William Turrell）

cat *.sql  > all_files.sql1
mv all_files.sql1 all_files.sql

回答by StanleyD

This is the easiest way that I have found.

这是我找到的最简单的方法。

In Windows (powershell):

在 Windows (powershell) 中：

cat *.sql | C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe -u user -p database

cat *.sql | C:\wamp64\bin\mysql\mysql5.7.21\bin\mysql.exe -u user -p database

You will need to insert the path to your WAMP - MySQLabove, I have used my systems path.

您需要插入WAMP - MySQL上面的路径，我已经使用了我的系统路径。

In Linux (Bash):

在 Linux (Bash) 中：

cat *.sql | mysql -u user -p database

cat *.sql | mysql -u user -p database

回答by PHP Kishan

1. Goto cmd

2. Type in command prompt C:\users\Usersname>cd [.sql tables folder path ]
   Press Enter
   Ex: C:\users\Usersname>cd E:\project\database

3. Type command prompt
   C:\users\Usersname>[.sql folder's drive (directory)name]
   Press Enter
   Ex: C:\users\Usersname>E:

4. Type command prompt for marge all .sql file(table) in a single file
   copy /b *.sql newdatabase.sql
   Press Enter
   EX: E:\project\database>copy /b *.sql newdatabase.sql

5. You can see Merge Multiple .sql(file) tables Files Into A Single File in your directory folder
   Ex: E:\project\database

1. 转到 cmd

2. 键入命令提示符 C:\users\Usersname>cd [.sql 表文件夹路径]
   按 Enter
   例如：C:\users\Usersname>cd E:\project\database

3. 键入命令提示符
   C:\users\Usersname>[.sql 文件夹的驱动器（目录）名称]
   按 Enter
   例如：C:\users\Usersname>E：

4. 为 marge all .sql file(table) in a single file
   copy /b *.sql newdatabase.sql键入命令提示符
   按 Enter
   EX：E:\project\database>copy /b *.sql newdatabase.sql

5. 您可以在目录文件夹中看到 Merge Multiple .sql(file) tables Files Into A Single File
   例如：E:\project\database

回答by DragonYen

I know it's been a little over two years... but I was looking for a way to do this, and wasn't overly happy with the solution posted (it works fine, but I wanted a little more information as the import happens). When combining all the SQL files in to one, you don't get any sort of progress updates.

我知道已经两年多了……但我一直在寻找一种方法来做到这一点，并且对发布的解决方案并不太满意（它工作正常，但在导入时我想要更多信息） . 将所有 SQL 文件合并为一个时，您不会获得任何类型的进度更新。

So I kept digging for an answer and thought this might be a good place to post what I found for future people looking for the same answer. Here's a command line in Windows that will import multiple SQL files from a folder. You run this from the command line while in the directory where mysql.exe is located.

所以我一直在寻找答案，并认为这可能是一个发布我发现的内容的好地方，供未来寻找相同答案的人使用。这是 Windows 中的一个命令行，它将从一个文件夹中导入多个 SQL 文件。您可以在 mysql.exe 所在的目录中从命令行运行它。

for /f %f in ('dir /b <dir>\<mask>') do mysql --user=<user> --password=<password> <dbname> < <dir>\%f

With some assumed values (as an example):

使用一些假设值（例如）：

for /f %f in ('dir /b c:\sqlbackup\*.sql') do mysql --user=mylogin --password=mypass mydb < c:\sqlbackup\%f

If you had two sets of SQL backups in the folder, you could change the *.sql to something more specific (like mydb_*.sql).

如果文件夹中有两组 SQL 备份，则可以将 *.sql 更改为更具体的内容（如 mydb_*.sql）。

回答by Markus Zeller

Enter the mysql shell like this.

像这样进入mysql shell。

mysql --host=localhost --user=username --password --database=db

mysql --host=localhost --user=username --password --database=db

Then use the sourcecommand and a semicolon to seperate the commands.

然后使用源命令和分号来分隔命令。

source file1.sql; source file2; source file3;

源文件1.sql；源文件2；源文件3；

回答by Tarek

Save this file as .bat and run it , change variables inside parenthesis ...

将此文件另存为 .bat 并运行它，更改括号内的变量...

@echo off
title Mysql Import Script
cd (Folder Name)
 for %%a in (*) do (
     echo Importing File  : %%a 
     mysql -u(username) -p(password)  %%~na < %%a
)
pause

if it's only one database modify (%%~na) with the database name .

如果只有一个数据库修改 (%%~na) 与数据库名称。

回答by BlackCharly

The easiest solution is to copy/paste every sql files in one.

最简单的解决方案是将每个 sql 文件复制/粘贴到一个文件中。

You can't add some sql markupfor file importation (the imported files will be in your computer, not in the server, and I don't think MySQL manage some import markupfor external sql files).

您不能为文件导入添加一些sql 标记（导入的文件将在您的计算机中，而不是在服务器中，而且我认为 MySQL 不会为外部 sql 文件管理一些导入标记）。

回答by yan

just type:

只需输入：

cat *.sql |mysql -uroot -p

and mysql will import all the sql file in sequence

并且mysql会依次导入所有的sql文件

回答by Ajay Singh

Just type below command on your command prompt & it will bind all sql file into single sql file,

只需在命令提示符下键入以下命令，它将所有 sql 文件绑定到单个 sql 文件中，

c:/xampp/mysql/bin/sql/ type *.sql > OneFile.sql;