Computers are dumb. And they will do exactly what you ask them to do. The trick often is to think as dumb as the computer. Sadly it is all to easy to assume that the computer is 'thinking' like you are and blunder into a head scratching puzzle. Recently there was a post on MySQL Community Space Groundbreakers Developer Community site that shows that sometimes what is intended is not what you want but you are getting exactly what you asked.
Quiz -- What happens if you run the following query?
TABLE_NAME,
' like sakila.',
TABLE_NAME, ';')
FROM information_schema.`TABLES`
WHERE TABLE_SCHEMA = 'sakila'
A) You will create copies of the tables in the sakila schema in sakila1 schema.
B) You will create the SQL queries to create copies of the tables in the sakila schema in the sakila1 schema.
C) This will fail as you have to create sakila1 before you can run this query.
D) This query has a syntax error
Your answer??
To help you take this quiz, here is the output of the query. And yes, I have sakila schema but not a sakila1 schema.
The author of this query was expecting the query to create copies of all the tables in the sakila schema in the sakila1 schema.
Since the query ran, we can eliminate D as an answer.
And since it ran without the sakila1 schema already created, we can eliminate C.
A simple SHOW SCHEMAS will show that A is not the answer. Yes, even if you create the new schema before running the query.
The answer is that this query creates the query to do the work but does not perform the actual work. So the query does exactly as asked but not as intended.
So what if you want this new schema?
Well, you could save the output from the query in a file and then execute that file.
Or put the query above in a CTE or subquery and use the output to do the intended work.
