I have a script to run in my database. But the problem is this script assume the tables have no prefix on it and all databases have a prefix (let call it prefix_).
Is there a command or a way to MySQL try to run
INSERT INTO prefix_mytable ...
instead of
INSERT INTO mytable...
for all of sql queries at the script (UPDATE, INSERT and DELETE)?
There is no way in MySQL to automatically prefix tables in the way you're describing. @MichaelBerkowski is correct.
The best I can suggest is that you create a second database with updateable views, using unprefixed names, as front-ends to your prefixed table names.
Here's an example:
mysql> CREATE DATABASE test;
mysql> CREATE TABLE test.prefix_mytable (id INT PRIMARY KEY, x VARCHAR(20));
mysql> CREATE DATABASE test2;
mysql> CREATE VIEW test2.mytable AS SELECT * FROM test.prefix_mytable;
Now you can insert using the unprefixed names:
mysql> INSERT INTO test2.mytable (id, x) VALUES (123, 'abc');
And to verify that the data was inserted into your original table:
mysql> SELECT * FROM test.prefix_mytable;
Once you do that, you can run your SQL script against database test2 and all the INSERTs should get to your original tables all right.
If you have a lot of tables you need to create views for, you can automate the creation of the CREATE VIEW statements:
mysql> SELECT CONCAT('CREATE VIEW test2.', REPLACE(TABLE_NAME, 'prefix_', ''),
' AS SELECT * FROM test.', TABLE_NAME, ';') AS _sql
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='test' AND TABLE_NAME LIKE 'prefix\_%';