Is there a way that I can import a database that have a data in it and export it into a same database but with difference data type on their fields. Like in the database A with table of user have postal_code with data types int and i want to export the database A and import to the database B table of user but the postal_code is varchar.
So all i want is to get the data from a database to send to other database that have similar table and field. Just some difference data types.
Is that possible?
It's not strictly necessary to export and import the data to make this change.
You can even ALTER TABLE to change a column's data type in place, without export and import. If you change the data type and all the values can be easily cast to a value in the new data type, then they just convert.
But you may want to export and import as separate steps, just to have extra assurance you won't clobber the original data.
To do it with full export and import, here's how I would do it:
Get the table only, without data:
mysqldump --no-data --skip-add-drop-table myschema mytable > ddl.sql
Get the data only, without table DDL, and save that in a separate file:
mysqldump --no-create-info myschema mytable > data.sql
Restore the table to a test schema (or any other schema besides the original one). Be careful not to run this against your original schema; that's why I used --skip-add-drop-table so it wouldn't accidentally drop your original table if you restored to the original schema by mistake.
mysql test < ddl.sql
Change the data type:
mysql test -e "ALTER TABLE mytable MODIFY COLUMN mycolumn VARCHAR(32)"
Double-check that it looks like what you want:
mysql test -e "SHOW CREATE TABLE mytable"
Restore the data:
mysql test < data.sql
This should work. The numeric values in the data dump are compatible with a VARCHAR column, so they should just import and be stores as string data instead of integers.
Double-check that the data look like what you want:
mysql test -e "SELECT * FROM mytable LIMIT 10"
Now you can swap the new table for the original one:
mysql -e "RENAME TABLE myschema.mytable TO test.original_mytable,
test.mytable TO myschema.mytable"
This can be done instantaneously. Renaming tables is similar to mv to move a file in the shell. It doesn't need to copy the whole tables, it just changes their name, so it doesn't take any longer no matter how large the table is.
Also by doing both renames in one statement, they are swapped atomically, so any queries against the original table from other clients will never see any moment where the table is missing.
Pro Tip: I never use MySQL Workbench for data export/import tasks. I know it has a menu item for doing that, but it's grievously buggy and slow. I don't know why they even bother to have that feature in the GUI tool. There are even bugs reporting that it can fail to import all data!
You should read bug reports like the following before deciding to use MySQL Workbench for this task:
I will only use MySQL command-line tools for data export/import.