The World of MySQL & MariaDB

How to Run SQL Files in MySQL: Step-by-Step Guide

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 for the following:
  • Automated execution of multiple SQL statements: You can run multiple commands such as creating tables, inserting data, and manipulating records in a single batch.
  • Streamlined backup and maintenance: Simplifies database backups and regular updates while minimizing manual tasks.

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: Editors with syntax highlighting such as Visual Studio Code or Notepad++ make SQL files easier to read.

3. Three Ways to Execute SQL Files in MySQL

3.1 Executing SQL Files Using the source Command

  • Command syntax
  source /path/to/yourfile.sql
  • Steps to use
  1. After connecting to MySQL, enter the source command with the path to your SQL file.
  2. Once executed, the results or error messages will be displayed.
  • Note: Use either an absolute or relative path for the file. Depending on your environment, additional permissions may be required.

3.2 Executing SQL Files with the . Command

  • Command syntax
  . /path/to/yourfile.sql
  • Difference: Similar to the source command, but may cause errors when using semicolons, so caution is required.

3.3 Direct Execution of SQL Files Using the mysql Command

  • Command syntax
  mysql -u username -p < /path/to/yourfile.sql
  • Description: Can be run directly from the terminal or command prompt without logging into MySQL. It is also suitable for automation when combined with shell scripts.

4. Checking Results and Handling Errors

  • How to verify results Use SHOW TABLES; or SELECT * FROM to check whether tables were created and data was inserted successfully.
  • Resolving error messages
  • Path errors: Verify that the specified file path is correct.
  • Access permission errors: Check MySQL user permissions, as insufficient privileges may cause issues.

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

  • Database backup and restore All table data can be stored in SQL files, making it easy to manage backups.
  • Data migration Useful for transferring data between production and development environments. For smooth imports, apply preprocessing and error checks to ensure consistency in file content.

6. Summary and Key Points

While SQL file execution is convenient, it is crucial to ensure data safety. In production environments, be mindful of environment differences to prevent unexpected data changes.