基于EER图创建的MySQL数据库，如何生成数据填充语句脚本？

Hey there! Since you're new to MySQL and already have your tables populated with Excel data, generating a script full of all your data INSERT statements is totally straightforward—here are a few tailored methods to get this done:

Method 1: Use MySQL Workbench's Graphical Export Tool (Beginner-Friendly) #

Since you used the EER diagram feature in Workbench, this is the most intuitive approach:

• Launch MySQL Workbench and connect to your database server.
• In the left sidebar, find your target database, right-click it, and select "Data Export".
• In the export window, check off the tables you need INSERT scripts for (tick the top box to select all tables at once).
• Under "Export Options", pick "Dump Data Only" if you only want the INSERT statements (skip table creation code). If you want both table structure and data, choose "Dump Structure and Data".
• Select a save location for your SQL file, then hit "Start Export". Once finished, you’ll have a script file packed with all your data insertion commands.

Method 2: Use the mysqldump Command Line Tool (Server-Side Operation) #

If you’re working directly on the server, the mysqldump utility is perfect:

• Open a terminal or command prompt on your server.
• Run this command (replace placeholders with your actual credentials and database name):

  mysqldump -u your_username -p your_database_name --no-create-info > data_insert_script.sql
  

  • The --no-create-info flag tells mysqldump to skip table creation SQL and only export INSERT statements. Omit this flag if you want to include both structure and data.
• Enter your MySQL password when prompted, and the script file will be created in your current directory.

Method 3: Generate INSERT Statements for a Single Table (Quick One-Off) #

If you only need data from one specific table, use a SQL query to build the INSERT lines directly:

• Run this query in your MySQL client (replace your_table_name and column names with your actual table details):

  SELECT CONCAT(
    'INSERT INTO your_table_name (column1, column2, column3) VALUES (',
    QUOTE(column1), ', ', QUOTE(column2), ', ', QUOTE(column3), ');'
  ) AS insert_statement
  FROM your_table_name;
  

• Copy all results from the insert_statement column, paste them into a text file, and save it as a .sql script. This works best for small tables—stick to the first two methods for larger datasets.

Quick Tips #

• Double-check character encoding when exporting to avoid data garbling (most tools let you specify UTF-8, which is safe for non-ASCII characters from your Excel file).
• If your tables have auto-incrementing primary keys, decide whether you want to include those values in INSERT statements—export tools usually let you toggle this setting.

内容的提问来源于stack exchange，提问作者Olga Tcacenco