Practical MySQL & MariaDB

How to Execute an SQL File in MySQL: 3 Practical Methods with Examples

Basic Operations
目次

1. Introduction: Benefits of Executing SQL Files

Using SQL files in MySQL is an efficient way to execute multiple SQL commands at once. This method is particularly useful in the following situations:

  • Automated execution of multiple SQL statements: You can batch execute multiple commands such as creating tables, inserting data, and manipulating data.
  • Improved efficiency for backups and maintenance: It simplifies database backups and periodic data updates, minimizing manual work.

2. How to Create an SQL File

First, write the SQL commands you want to execute in a text editor and save the file with a .sql extension.

  • Basic file structure: Below is a simple example of an SQL file that creates a table and inserts data.
  CREATE TABLE sample (
      id INT NOT NULL AUTO_INCREMENT,
      name VARCHAR(50),
      age INT,
      PRIMARY KEY (id)
  );

  INSERT INTO sample (name, age) VALUES ('Tanaka', 25);
  • Choosing an editor: Using editors with syntax highlighting such as Visual Studio Code or Notepad++ makes the file easier to read and manage.

3. Three Ways to Execute an SQL File in MySQL

3.1 Executing an SQL File Using the source Command

  • Command syntax
  source /path/to/yourfile.sql
  • Procedure
  1. After connecting to MySQL, enter the source command and specify the path to the SQL file you want to execute.
  2. Once execution is complete, the results or any error messages will be displayed.
  • Notes: Specify either an absolute path or a relative path for the file. Depending on the environment, appropriate access permissions may be required.

3.2 Executing an SQL File Using the \. Command

  • Command syntax
  \. /path/to/yourfile.sql
  • Difference: This method works almost the same as the source command, but be aware that using a semicolon may cause an error in some cases.

3.3 Executing an SQL File Directly with the mysql Command

  • Command syntax
  mysql -u username -p < /path/to/yourfile.sql
  • Description: This method allows you to execute the SQL file directly from the terminal or command prompt without logging into the MySQL interactive shell. It is well suited for periodic execution and automation when combined with shell scripts.

4. Verifying Execution Results and Handling Errors

  • How to verify results
    Use SHOW TABLES; or SELECT * FROM to confirm that tables were created correctly and data was inserted as expected.
  • Resolving error messages
  • Path errors: Check whether the specified file path is correct.
  • Permission errors: This may indicate insufficient privileges. Verify the permissions granted to the MySQL user.

5. Practical Use Cases: Streamlining Scheduled Tasks and Data Migration

  • Database backup and restore
    By saving all table data into an SQL file, you can easily manage backups and restore the database when necessary.
  • Data migration
    This is useful for transferring data between production and development environments. To ensure consistency during data import, perform preprocessing and error checking in advance for smoother execution.

6. Summary and Important Notes

While taking advantage of the convenience of executing SQL files, it is essential to carefully consider data safety. Especially when running SQL files in a production environment, pay close attention to environmental differences and prevent unintended data modifications.