Jump to content
Slate Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate Marble
Slate Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate Marble

Recommended Posts

Κάποιες φορές τυχαίνει να πρέπει να ξανακάνω import σε βάση από SQL data που δεν περιέχουν DROP statements

Εκει η διαδικασία λογικά σταματά νωρίς με κάτι τέτοιο:

"ERROR 1050 (42S01) at line χχ: Table 'db_table' already exists"

αντί να κάνουμε DROP την βάση / recreate και ξανά recreate user connections υπάρχει και το ακόλουθο που παράγει όλα τα DROP statements για κάθε πίνακα της βασης:

SELECT CONCAT('DROP TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA = '{database name}';

θα δείτε κάτι τέτοιο ( παράδειγμα από WP )

+----------------------------------------+
| CONCAT('DROP TABLE ', TABLE_NAME, ';') |
+----------------------------------------+
| DROP TABLE wpdbase_commentmeta;            |
| DROP TABLE wpdbase_comments;               |
| DROP TABLE wpdbase_links;                  |
| DROP TABLE wpdbase_options;                |
| DROP TABLE wpdbase_pmxi_history;           |
| DROP TABLE wpdbase_postmeta;               |
| DROP TABLE wpdbase_posts;                  |
| DROP TABLE wpdbase_term_relationships;     |
| DROP TABLE wpdbase_term_taxonomy;          |
| DROP TABLE wpdbase_termmeta;               |
| DROP TABLE wpdbase_terms;                  |
| DROP TABLE wpdbase_usermeta;               |
| DROP TABLE wpdbase_users;                  |
| DROP TABLE wpdbase_wysija_campaign;        |
| DROP TABLE wpdbase_wysija_campaign_list;   |
| DROP TABLE wpdbase_wysija_custom_field;    |
| DROP TABLE wpdbase_wysija_email;           |
| DROP TABLE wpdbase_wysija_email_user_stat; |
| DROP TABLE wpdbase_wysija_email_user_url;  |
| DROP TABLE wpdbase_wysija_form;            |
| DROP TABLE wpdbase_wysija_list;            |
| DROP TABLE wpdbase_wysija_queue;           |
| DROP TABLE wpdbase_wysija_url;             |
| DROP TABLE wpdbase_wysija_url_mail;        |
| DROP TABLE wpdbase_wysija_user;            |
| DROP TABLE wpdbase_wysija_user_field;      |
| DROP TABLE wpdbase_wysija_user_history;    |
| DROP TABLE wpdbase_wysija_user_list;       |
+----------------------------------------+
28 rows in set (0.00 sec)

και εύκολα μετα προχωράτε σε διαγραφή τους με ένα απλό copy paste

εχω βρει και άλλες λύσεις και σίγουρα θα υπάρχει και καλύτερη

http://stackoverflow.com/questions/27606518/how-to-drop-all-tables-from-a-database-with-one-sql-query

http://stackoverflow.com/questions/536350/drop-all-the-tables-stored-procedures-triggers-constraints-and-all-the-depend

http://stackoverflow.com/questions/11053116/mysql-bulk-drop-table-where-table-like

 

 

 

  • Like 2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×