Geek asks and answers

Routing traffic to a particular MySQL connection depending on the type of query and table

For what I thought it would be a common problem after the average search volume returned nothing. I have several mysql servers with different tables on them, and each of them is either a master or a readable subordinate. I would like to direct all SELECT queries and others not related to the table using the reader slave and all the rest INSERT, UPDATE, ALTER, etc. to the master, and also make sure that the right main subordinate combo has tables that I ask for.

TL; DR : based on tables and query type (read or write) I want to use another mysql connection.

To do this in a clean way, I create a wrapper function that will check the request and decide which connection to use. This function will provide detailed information about which tables are on the servers and which are subordinate or master. All I will pass is a string containing the sql statement. I know how to implement this with regular expressions, but I would prefer to use a built-in function if it is possible to take into account any variation in SQL syntax or the possibility of operator types that I do not know about.

TL; DR : is there a better way than regular expression to get table name and query type from sql query string? What is an exhaustive list of sql read operations? Does anyone have a good regex to find parts of an sql statement for all variations of SQL queries?

I would suggest that many people faced a similar problem when creating their systems, and at least some of them solved it in a similar way. Help in any of these areas would be greatly appreciated. Thank you for your time.

+3
source share
2 answers

ORM, Doctrine Propel , , SQL.

, SELECT Doctrine, - :

$users = UserTable::getInstance()
  ->createQuery()                      // SELECT is the default operation.
  ->andWhere('is_active = ?', $active)
  ->fetch();

, .

+1

More articles:


All Articles