I have a table, named
servers, with a single column
This table is accessed by various machines each with a different username for the database.
I want to make it so each server does not see all the information on this table, but only a subset I designate to it in order to partition the workload among the app servers.
My original idea is to rename the table (say, to "servers_table"), add another "user" column to it, and create a view (with the original name) that shows the results based on the username currently connected to the server.
My table currently looks like this:
id user 1 server1 2 server1 3 server2 4 server1 5 server2
My attempt was (as "server1"):
CREATE VIEW server AS SELECT id FROM server_orig WHERE usuario=SUBSTRING_INDEX(CURRENT_USER(),'@',1);
This, however, does not work, since
CURRENT USER() is evaluated at
CREATE VIEW time, not at
mysql -userver1 database -e "select * from server;"
+----+ | id | +----+ | 1 | | 2 | | 4 | +----+
BUT so does running it as server2..
I cannot (since the program is closed source) change the original
select statement (and that would also incur eventually changing the
insert/update statements too).
Is there a way this can be accomplished purely on the MySQL side?