Customize your SQL Writing

MySQL has many SQL Modes, and these modes rarely had been considered by SQL Developers, and my be they came from ORACLE programming or SQL Server which they didn’t have these modes.

Modes in MySQL are so important and must be considerable especially when they embed queries in their quires, to prevent sql injection for an example.

The MySQL server can operate in different SQL modes, and can apply these modes differently for different clients.

Ways to set different sql modes:

• Start MySQL server with --sql-mode=value[,value,value,…].
• Sql_mode= value[,value,value,…] Inside my.cnf (under unix) or my.ini (under windows)
• By mysql client tool

Select @@global sql_mode= value[,value,value,…]; (effect on the whole connections)
Select @@session sql_mode= value[,value,value,…]; (effect just in the same session)

I’ll describe some sql modes here and you can find all the sql modes in mysql manual online:

• STRICT_TRANS_TABLES

If a value could not be inserted as given into a transactional table, abort the statement. For a non-transactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement

• TRADITIONAL

Make MySQL behave like a “traditional” SQL database system. A simple description of this mode is “give an error instead of a warning” when inserting an incorrect value into a column. Which we always wondering why I didn’t get error for that just a warning ;)

• ALLOW_INVALID_DATES

Don't do full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This is very convenient for Web applications where you obtain year, month, and day in three different fields and you want to store exactly what the user inserted (without date validation).

• ANSI_QUOTES

Treat “"” as an identifier quote character (like the “`” quote character) and not as a string quote character. You can still use “`” to quote identifiers with this mode enabled. With ANSI_QUOTES enabled, you cannot use double quotes to quote literal strings, because it is interpreted as an identifier.

• NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a non-empty password also is specified

• ONLY_FULL_GROUP_BY

Do not allow queries for which the SELECT list refers to non-aggregated columns that are not named in the GROUP BY clause. The following query is invalid with this mode enabled because address is not named in the GROUP BY claus.

This one when set sql queries will look like ORACLE standard queries.

Huh?!! MySQL has what other engines have and more, so by setting one or more sql modes you can customize you sql writing language. It is so great, isn’t?