mysql-pdo模式,使用PDO时如何设置SQL模式？

I am trying to set SQL modes, I can't figure out how to do it using PDO. I am trying to set Traditional mode in MySQL and not allow invalid dates.

Can anyone help?

解决方案

You can use the optional 'SESSION' variable when setting the sql_mode at runtime. That way it won't affect other clients. You can set the SESSION sql_mode and then set it back to the previous value after your query has completed. In this way, you can set the sql_mode for a specific operation.

From the MySql manual:

"You can change the SQL mode at runtime by using a SET

[GLOBAL|SESSION] sql_mode='modes' statement to set the sql_mode system

value. Setting the GLOBAL variable requires the SUPER privilege and

affects the operation of all clients that connect from that time on.

Setting the SESSION variable affects only the current client. Any

client can change its own session sql_mode value at any time."

I personally added some methods to my database class to handle this. initSqlMode() will execute the query 'SELECT SESSION.sql_mode' and store the default value as a class variable. setSqlMode() will allow you to set the SESSION sql_mode to a (VALIDATED) custom value. resetSqlMode() sets the SESSION sql_mode back to the default value. I use the SESSION variable when manipulating the sql_mode at all times.

Then you can do something like the following. Note this is only psuedocode; there is nothing in my example to prevent sql injection or parameterize the sql query.

$db = new database();

$badqueryresult = $db->executeStrict('BAD SQL QUERY');

Class database {

...

function executeStrict($query){

$this->initSqlMode();

$this->setSqlMode('STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION');

$result = $this->Execute($query);

$this->resetSqlMode();

return $result;

}

}