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

Question

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.

spencer7593 · Accepted Answer · 2017-03-12 03:27:02Z

1

@@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 ;

answered Mar 12, 2017 at 3:27

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Buddhika Alwis · Accepted Answer · 2017-03-12 07:20:03Z

0

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';

edited Mar 12, 2017 at 7:20

Buddhika Alwis

37115 silver badges23 bronze badges

answered Mar 12, 2017 at 2:35

user5746286

2 Comments

Michael - sqlbot Over a year ago

You can just use SET @@SESSION.SQL_MODE = @@GLOBAL.SQL_MODE; to put it back. Technically SET @@SQL_MODE = @@GLOBAL.SQL_MODE; does the exact same thing, but I like the former version because it's unambiguous to the next poor sucker reading the code, what you were doing.

Michael - sqlbot Over a year ago

Or, store the current value in a user-defined variable before, and then put it back, after... SET @_SQL_MODE = @@SQL_MODE; before and SET @@SQL_MODE = @_SQL_MODE; after will teporarily store the current value in a variable called _SQL_MODE (where a single @ is the sigil for a user-defined variable, which does not need to be declared).

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related