mysql import error

MySQL is a popular open source database management system that is widely used in website development, mobile applications, enterprise applications and other fields. When using MySQL, sometimes import errors may occur, such as incorrect syntax when importing data, incorrect format of imported files, etc. These problems may result in data loss or system crash.

In this article, we will discuss how to properly import data in MySQL to avoid import errors. Next, we will discuss this topic in the following parts:

1. Preparation before importing
2. Importing data using the command line
3. Importing data using graphical tools
IV. Common import errors and their solutions

1. Preparation before import

Before importing data into MySQL, we need to complete some preparation work to ensure that the imported data can be handled correctly. The following are some preparations:

1. Create the target database and table

Before creating a table in the database, you need to create a target database first. A database can be created in MySQL using the following command:

CREATE DATABASE database_name;

Then, tables need to be created in that database using the following command:

CREATE TABLE table_name (
column1_name datatype,
column2_name datatype,
column3_name datatype,
.....
);

2. Prepare the data file

Before importing data, you need to ensure that the data file to be imported has been prepared. Data can be saved to a file using the following command:

SELECT * INTO OUTFILE 'file_name' FROM table_name;

If the data is saved in a CSV or Excel file, then You need to ensure that the file conforms to the format required by MySQL import.

3. Confirm MySQL configuration

Before importing data, please confirm whether MySQL configuration is correct. In particular, you need to confirm whether the maximum allowed file size of MySQL is higher than the size of the file to be imported, otherwise it may cause an import error.

2. Use the command line to import data

Using the command line to import data is one of the most commonly used methods in MySQL. The following are the specific steps:

1. Open the command line

In Windows systems, you can press the Win R key to open the run dialog box, enter cmd and press the Enter key. Can open the command line. In Linux and macOS systems, you can open a terminal and enter the following command to enter MySQL or MariaDB:

$ mysql -u username -p database_name

where username is the MySQL username and database_name is The name of the database into which data is to be imported.

2. Select database

After entering the MySQL terminal, you need to select the target database to import data. You can use the following command:

USE database_name;

Among them, database_name is the name of the database to which data is to be imported.

3. Import data

Next, you can use the following command to import the data into the target database:

LOAD DATA INFILE 'file_name' INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 ROWS;

where, file_name is the name of the file to be imported, table_name is the name of the table to be imported, FIELDS TERMINATED BY ',' means commas are used to separate fields, ENCLOSED BY '"' means double quotes are used to include data, LINES TERMINATED BY '
' means the newline character is
. IGNORE 1 ROWS means to ignore the first row of data, because the first row is usually the title of the table.

3. Use graphical tools to import data

In addition to using the command line to import data, you can also use graphical tools to import, such as phpMyAdmin and Navicat. The following are the specific steps:

1. Open the graphical tool

Open the graphical tool and log in to the MySQL server.

2. Select the target database

Select the table to import data in the target database.

3. Select the import method

In the Import tab, select the file type you want to import (CSV, Excel, etc.) and select the file you want to import.

4. Confirm import settings

For import settings, please confirm that field delimiters, text delimiters, etc. are consistent with your file format. Finally, click the Start Import button to import the data into MySQL.

4. Common import errors and their solutions

When importing data into MySQL, the following common errors may occur. We also provide suggestions for resolving these errors.

1. File not found

If a file not found error occurs when importing, you need to confirm whether the file path is correct and make sure the file already exists. In addition, you need to ensure that MySQL has permission to access the file. You can use the following command to see if the target path is correct:

SHOW VARIABLES LIKE 'secure_file_priv';

If the output is empty, it means there is no limit.

2. Table does not exist

If a table does not exist error occurs during import, you need to confirm whether the table name is correct and exists in the imported target database.

3. Data type mismatch

When importing data, you need to ensure that the data type of the imported data matches the field data type in the table, otherwise it may cause an import error. It is recommended that before importing data, you first use the DESC command to view the structure of the table and compare it with the data to be imported to ensure that the data types match.

4. Incorrect syntax

If a syntax error occurs when importing, you need to check whether the syntax in the imported file is correct. It is recommended to use a text editor to check the file for obvious errors.

Summary

Importing data in MySQL is a very common operation, but it is also prone to some errors. To avoid import errors, you need to make preparations before importing, ensure that MySQL is configured correctly, and choose the correct import method (command line or graphical tool). If an error occurs, we need to carefully check the error message and perform corresponding solutions.

The above is the detailed content of mysql import error. For more information, please follow other related articles on the PHP Chinese website!