Recently, I wrote several articles on how to load data from CSV files to migrate from different databases to MySQL Database Service:
- How to import data from PostgreSQL to MySQL Database Service
- How to import data from Amazon Redshift to MySQL Database Service
- How to import data from Microsoft SQL Server to MySQL Database Service
- Import data from Amazon Aurora PostgreSQL to MySQL Database Service in OCI
We saw that the most complicate is to write the
CREATE TABLE statement that matches the data. I also received some questions about how to generate the table’s definition when only the CSV file was available.
I wrote a MySQL Shell plugins that helps generating a
CREATE TABLE statement from a CSV file:
Let’s have a look to how it works:
This new method accepts the following parameters:
- filename: the CSV file path.
- delimiter: the field delimiter.
- column_name: (boolean) – use the first row as column name.
- first_as_pk: (boolean) – use the first column as Primary Key.
- pk_auto_inc: (boolean) – the PK will be defined as
int unsigned auto_increment. If
first_as_pkis disabled, a new column will be added but invisible.
Let’s have a look at examples using some of these options:
In the example above, we don’t one to use the first column as Primary Key but we want to have an auto_increment as Primary Key. Therefor, an invisible Primary Key is created to be able to load the data into the table:
We where able to load the csv file and we can retrieve data from it:
As you can see the new Primary Key is invisible bit we can of course retrieve it:
It’s also possible to not use the first row as column names:
Once again, it’s very easy to extend MySQL Shell. If you do and you want to share your plugin(s), don’t hesitate to submit a Pull Request to my GitHub repository.
Enjoy MySQL and MySQL Shell !
[…] use MySQL Shell with a plugin to generate the create statement for […]