How to Convert a MySQL Dump File for Import into SQLite3?

Converting MySQL Dump File for Import into SQLite3

Importing a MySQL dump file directly into a SQLite3 database may encounter difficulties. To address this, a shell script is available to facilitate the conversion process.

The provided script performs the following transformations to the dumped SQL file:

• Removes references to keys (e.g., "PRIMARY KEY", "UNIQUE KEY")
• Converts SQL statements starting with "SET" to comments
• Replaces "unsigned" with whitespace
• Changes "auto_increment" to "primary key autoincrement"
• Converts "smallint", "tinyint", and "int" to "integer"
• Removes character set specifications
• Replaces "enum" data types with "varchar(255)"
• Removes "on update" clauses
• Splits multi-line "INSERT" statements into individual lines

The transformed output is then passed through Perl to add "begin;" and "commit;" statements, ensuring the conversion remains atomic. Finally, the converted SQL file is executed within "sqlite3", and the resulting database is saved as ".db" while potential errors are logged to ".err".

To use the script, follow these steps:

1. Save the script as a file (e.g., "convert.sh")
2. Make the script executable using "chmod x convert.sh"
3. Run the script with the argument as your MySQL dump file (e.g., "convert.sh my_dump.sql")

Once the process completes, check for any errors in the ".err" file. If no errors are present, the database with the imported data can be found in the ".db" file.

The above is the detailed content of How to Convert a MySQL Dump File for Import into SQLite3?. For more information, please follow other related articles on the PHP Chinese website!