How to execute mulitple statement SQL queries using $wpdb->query?

TL;DR

This is not possible with wpdb. Use dbDelta(). Even better, use something else to run raw queries.

Explanation

wpdb->query() uses the mysql_query() function. From PHP manual:

mysql_query() sends a unique query (multiple queries are not supported) to the currently active database on the server that’s associated with the specified link_identifier.

In order for that to work, wpdb would have to use mysqli_multi_query(). But you are dealing with WordPress here: it still runs with the deprecated mysql extension.

Solution 1

I imagined that WP would have something to aid with migrations, and apparently it does: dbDelta(). This is a 438-line-long monster, so I will save you some time:

It doesn’t give you a list of separated queries. It returns a list of updates that are performed by the queries – if you were to run them with the second parameter being true. Inside, it splits the query by semicolons, and then does such magix that it’s impossible to tell what happens. If used incorrectly, this function may open a portal into Oblivion. Looks like it actually skips certain queries, like those that create global tables. It will normalize whitespace, quote column names. If the query is creating WP tables that already exist, but something in the schema is different, such as indices or column types, it will try to alter the tables without re-creating them, so that they can match what your query would create. I do not know why it is able to successfully run queries where the semicolon doesn’t terminate a query, such as part of the string.

If you thought that you can use IoC and pass the wpdb object to your cool standardized interface implementation, then you’re out of luck.

Solution 2

Use another DB adapter or extension. In most cases, if you need to run migrations, such as create tables and insert many rows, it’s not a problem if you don’t use wpdb (and thus have to create another DB connection), because this is only going to happen very rarely. Also, due to the crazy transformations done by dbDelta(), it’s probably more reliable to find a way to run raw queries.

• mysqli_multi_query() is a function of the mysqli (not outdated) extension that can easily run multiple statements at once.
• PDO can run multiple queries in emulation mode.