code_junkie

How to empty all rows from all tables in mysql (in sql)

Nov 13th, 2011
142
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.90 KB | None | 0 0
  1. TRUNCATE tableName;
  2.  
  3. <?php
  4. mysql_connect('localhost', 'user', 'password');
  5. $dbName = "database";
  6. mysql_select_db($dbName)
  7. $result_t = mysql_query("SHOW TABLES");
  8. while($row = mysql_fetch_assoc($result_t))
  9. {
  10. mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);
  11. }
  12. ?>
  13.  
  14. mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
  15.  
  16. for j in database_name1 database_name2;
  17. do for i in `echo 'show tables ' |mysql $j
  18. |grep -v 'Tables_in'`; do mysql $j -e "truncate $i"; done; done
  19.  
  20. `echo 'show databases' | mysql | awk '$1 != "information_schema" &&
  21. $1 != "mysql" {if (NR > 1) {print}}'`
  22.  
  23. for j in `echo 'show databases' | mysql |
  24. awk '$1 != "information_schema" && $1 !=
  25. "mysql" {if (NR > 1) {print}}'`; do for i in
  26. `echo 'show tables ' |mysql $j |grep -v
  27. 'Tables_in'`; do mysql -e "optimize table $j.$i";
  28. done; done
Add Comment
Please, Sign In to add comment