我在PHP代码中做了类似的过滤。但是上下文是不同的;我们使用的是主从复制体系结构。为了将查询定向到相关服务器(将查询读取到从属服务器,并将查询写入主服务器),将编写一个自定义函数来标识
类型
查询。
值得注意的一点是,事务/锁定/解锁操作中的查询始终被视为写查询。
另外,对于
Set
,只有
SET AUTOCOMMIT
和
SET TRANSACTION
是写命令。
请在下面找到我们正在使用的实际代码的主要色调版本:
/*
* All the WRITE operation commands
*/
$write_commands = array(
'create',
'alter',
'drop',
'truncate',
'comment',
'rename',
'insert',
'update',
'delete',
'merge',
'call',
'lock',
'unlock',
'start',
'commit',
'rollback',
'savepoint',
'set',
'replace'
);
/*
* method to determine whether Read or Write
* @param $sql String (SQL query string)
* @return: void
*/
function determineReadOrWrite(string $sql): void {
$dml_query = false;
$words = str_word_count(strtolower(trim($sql)), 1);
$first_word = isset($words[0]) ? $words[0] : '';
$second_word = isset($words[1]) ? $words[1] : '';
if (in_array($first_word, $this->write_commands)) {
/* if it is not "set" then we set to master link */
if ($first_word !== 'set'
|| ($first_word === 'set' && $second_word === 'autocommit')
|| ($first_word === 'set' && $second_word === 'transaction')
) {
$dml_query = true;
/* If we Lock tables or Begin a Transaction, we should run on Write servers only */
/* till we Commit/Rollback or Unlock Tables */
if(($first_word === 'start' && $second_word === 'transaction')
|| $first_word === 'lock'){
/* Set whether the current query is starting a Transaction / Lock etc */
$this->wait_for_commit_rollback = true;
}
/* We are doing Commit/Rollback or Unlock Tables */
if ($first_word === 'commit'
|| $first_word === 'rollback'
|| $first_word === 'unlock') {
$this->wait_for_commit_rollback = false;
}
}
}
/* It's a insert/update/delete/etc query - to be run on Write Db only */
if ($dml_query || $this->wait_for_commit_rollback) {
$this->setActiveConnectionToWrite(true);
} else {
$this->setActiveConnectionToRead();
}
}
