CODEWITHSUNDEEP
mysqlvariablesset
Buddhika Alwis
Buddhika Alwis

Reputation: 371

how to set sql mode variable as a local variable in mysql

What i want thing is, i want to set the sql_mode variable as a local variable, not as a session or global variable. Reason to do that is i want to disapear the change of sql mode variable after one of query was executed. Below session and global are worked well, but this is not the what i want. Global one is kept the sql mode as a empty one forever. Session one is kept the sql mode as a empty one until connection close. I want thing is, keep the sql mode until a quarry is executed only. 

mysql> set global sql_mode='';
mysql> set session sql_mode='';

mysql query :-

SELECT tc_exe_grp_num,tcs.tc_tc_id,tcs.tcs_id  
FROM tc_exe_res tcer 
INNER JOIN tcs tcs 
ON tcs.tcs_id = tcer.tcs_tcs_id  
WHERE tcs.tc_tc_id='1' 
AND tcs.tc_tc_id='1' 
GROUP BY tc_exe_grp_num 
ORDER BY tc_exe_grp_num ;

got the idea from this article

please help me.

Upvotes: 0

Views: 2658

Answers (2)

user5746286
user5746286

Reputation:

I couldn't find a direct answer for this, but there is a solution,

First set the "sql mode" as a empty one and after quarry was executed set the "sql mode" with what previously had values, try it in below way,

    set session sql_mode='';

    SELECT tc_exe_grp_num,tcs.tc_tc_id,tcs.tcs_id FROM tc_exe_res tcer INNER JOIN tcs tcs ON tcs.tcs_id = tcer.tcs_tcs_id WHERE tcs.tc_tc_id='1' AND tcs.tc_tc_id='1' group by tc_exe_grp_num ORDER BY tc_exe_grp_num ;

    set session sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Upvotes: 0

spencer7593
spencer7593

Reputation: 108490

@@sql_mode is session variable, not a local variable.

It is possible to retrieve the current setting of sql_mode, and save it in a user-defined variable, and then later set sql_mode back to the original setting.

For example:

 -- save current setting of sql_mode in user defined variable
 -- change sql_mode to desired setting
 SET @SAVE_sql_mode = @@sql_mode ;
 SET @@sql_mode = 'NO_ENGINE_SUBSTITUTION' ; 

 -- subsequent operations run under new setting of sql_mode
 SELECT '...';


 -- set sql_mode back to saved setting
 SET @@sql_mode = @SAVE_sql_mode ;

Upvotes: 1

Related Questions