As long as the account in question doesn't have the
SUPER privilege, you could accomplish this with the global server variable
init_connect, which is...
A string to be executed by the server for each client that connects. The string consists of one or more SQL statements, separated by semicolon characters.
This is part of the global server configuration and gets executed right before processing of the first query sent by each new client connection (except for users with
SUPER, because if you broke this, you couldn't log in to fix it otherwise).
init_connect contents are executed by the server for all non-
SUPER users... however, we could construct a
CASE expression that uses the identity of the logged-in user via the
CURRENT_USER() function and evaluates to the appropriate
sql_mode for that user, and otherwise use the default.
The expression might be something like this:
mysql> SET @@sql_mode = CASE CURRENT_USER()
WHEN 'usera@%' THEN 'TRADITIONAL' # this user gets 'TRADITIONAL'
WHEN 'userb@localhost' THEN 'ANSI' # this user gets 'ANSI'
ELSE @@sql_mode # everybody else gets the default value,
END; # from global sql_mode
Once you have the expression you want, wrap it up in string and assign it to the global variable
mysql> SET GLOBAL init_connect =
"SET @@sql_mode = CASE CURRENT_USER()
WHEN 'usera@%' THEN 'TRADITIONAL'
WHEN 'userb@localhost' THEN 'ANSI'
The same thing could then be added to the
[mysqld] section of my.cnf to make your configuration persist across restarts, although you'd almost certainly have to remove the whitespace I added for readability and possibly adjust the quoting.
Tested on MySQL Server 5.5.30. I attempted to benchmark whether this has any significant impact on initial connection time or first query execution time and my conclusion is that if there is an impact, it is most definitely in the sub-millisecond range and was too short to identify on the system under test.