0

I have a Java application that can send SQL queries to a MySQL server. I need to be able to truncate all tables form a specified database. So far I can retrieve the queries I need via a select statement, but I can't find a way to execute it in one single SQL statement. I can't know the schema beforehand because it constantly changes depending on the user. I have searched over StackOverflow but there is not a solution compliant to my problem or they don't work.

NOT THROUGH THE MYSQL API, ONLY SQL. I KNOW THERE ARE SIMILAR QUESTIONS BUT THEY DON'T WORK OR USE STORED PROCEDURES. I REQUIRE PLAIN SQL COMPATIBLE WITH MYSQL.

This is the SQL script I have so far:

SELECT CONCAT(
         'SET FOREIGN_KEY_CHECKS=0; ',
         GROUP_CONCAT(dropTableSql SEPARATOR '; '), '; ',
         'SET FOREIGN_KEY_CHECKS=1;'
       ) as dropAllTablesSql
FROM   ( SELECT  Concat('TRUNCATE TABLE ', table_schema, '.', TABLE_NAME) AS dropTableSql
         FROM    INFORMATION_SCHEMA.TABLES
         WHERE   table_schema = 'testschema' ) as queries
TheQuibbler
  • 13
  • 4
  • Possible duplicate of [Truncate all tables in a MySQL database in one command?](https://stackoverflow.com/questions/1912813/truncate-all-tables-in-a-mysql-database-in-one-command) – P.Salmon Oct 08 '19 at 10:45
  • @P.Salmon thanks for reading the post but they solve the issue in a different way that doesn't comply with my particular needs. – TheQuibbler Oct 08 '19 at 10:54
  • You can't execute the truncation of several tables with one sql statement, since each truncate table statement will be a separate one. So, your requirements may not be completely in line with the reality. The solutions described in the question linked by @P.Salmon are as close to a single statement as you can get. Really struggle to understand why those would not work for you. – Shadow Oct 08 '19 at 11:06
  • The syntax for truncate is TRUNCATE [TABLE] tbl_name - there is no ability to truncate many tables in one statement https://dev.mysql.com/doc/refman/8.0/en/truncate-table.html – P.Salmon Oct 08 '19 at 11:10
  • @Shadow thanks for replying but I had to do it in only one statement. I was looking if there is a possibility of executing the result of a SELECT statement. If you say it is not possible I'll have to figure it out somehow. – TheQuibbler Oct 08 '19 at 11:11
  • The question is tagged as java as well. You get the result of the select back as a string and execute the statements within it with java. Or use prepare - execute - deallocate prepare trio in mysql, but again you need 3 statements this way. – Shadow Oct 08 '19 at 11:27
  • @Shadow you are right, it can be done like so: `SET FOREIGN_KEY_CHECKS = 0; SELECT @str := GROUP_CONCAT('TRUNCATE TABLE ', table_schema, '.', table_name, ';' SEPARATOR ' ') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema IN ('testschema'); PREPARE stmt FROM @str; EXECUTE stmt; DEALLOCATE PREPARE stmt; SET FOREIGN_KEY_CHECKS = 1;` but the problem is that I get a syntax error in runtime, any ideas? – TheQuibbler Oct 10 '19 at 09:31

0 Answers0