Quickly solve the problems of incorrect format, slow import and data loss when importing data from MySQL

If you want to solve the slow problem once and for all, you might as well upgrade your MySQL to MySQL 8.0. The default character set of MySQL 8.0 has been changed from latin1 to utf8mb4, so now UTF8 is much faster, and the speed is increased by 1800% in certain queries!

But if time doesn’t wait, use the following method to quickly solve the problem.

Problem 1: Incorrect format (often the time format is incorrect);

Method 1: Save the excel file as csv and then import it into the database;

Method 2: In the first step of importing, the default encoding is 65001 (UTF-8). You can try to select [10008 (MAC - Simplified Chinese GB 2312)] or [Current Windows Codepage]. These are three common encodings. Try several times and you will always find an encoding format that matches your computer.

Method 3: In the last few steps of the import, you can set the type of the time column to datetime or time. In short, try to choose the type of the data. The default is varchar.

Problem 2: Slow import (due to large amount of data, please wait for it to finish importing);

Method 1: If the data is imported to you by someone else (whether it is a SQL file, a CSV file, or an Excel file), you will import it very slowly. The import speed is generally: SQL file>CSV file>Excel file. Then ask him to direct it for you again. No matter how he directed it before, please ask him to follow the steps below to redirect it again:

Open the table you want to export - there is an export button in the upper right corner of the opened table - use this export button;

Note: The [Export] mentioned above is different from the [Export Wizard] in the options of right-clicking a table. The table exported using the [Export Wizard] is sometimes slower, so I usually use the [Export] in the upper right corner to export data.

Problem 3: Data loss (generally, data loss occurs when using Excel);

Method 1: Save Excel as csv and then import;

Method 2: There may be a column with a large amount of data in the original data. For example, the last column of the grade table may be the teacher's comments, and there are too many comments in this column, which exceeds the default limit of MySQL for a single package (usually 255). Therefore, in the last step of the import, change the type of the comment column to text and the length to 65535. 65535 is the maximum length of the text type. If you can't remember it in daily life, just fill in 60000, which is basically enough and easy to remember.

Method 3: If data is lost during export, you may have selected "Excel Spreadsheet". Find the export method that suits you according to the figure below.

But if the above methods do not cover the problem you want to solve, then did you encounter a problem when exporting data from MySQL? You may want to take a look at the article below.

Quickly solve the problem of garbled characters and jump lines in mysql exported scv files

The above article on how to quickly solve the problems of incorrect format, slow import and data loss when importing data into MySQL is all the content that the editor shares with you. I hope it can give you a reference, and I also hope that you will support 123WORDPRESS.COM.