What is the SQL MODE in MYSQL (or any RDBMS)?

What is the SQL MODE in MYSQL (or any RDBMS)? Also what is the best option to have for the SQL MODE and Why?

If 开发者_StackOverflow中文版i could have a layman explanation with a example it would be great!

Thank you in advance ;-)

The SQL mode affects how MySQL behaves in certain situations. For example, it affects what happens if you try to insert overlong content into a text type column. Let's say you have a column like CHAR(10) CHARACTER SET ascii NOT NULL, and you try to insert 'abcdef abcdef'. That's 13 characters, which is too long. If the current SQL mode includes STRICT_TRANS_TABLES or STRICT_ALL_TABLES, MySQL will not insert the value but will give you an error message. If neither of these is on, then MySQL will truncate the string to 10 characters, i.e. 'abcdef abc', and insert that.

There are lots of other behaviours that are affected by the SQL mode. That was just an example.

I personally use TRADITIONAL, which is shorthand for, well, the link given by dcp will tell you. Basically, it makes MySQL less forgiving when you send it invalid input, which I find preferable because if I send MySQL something that's not valid I'd rather know about it, rather than have MySQL fudge it and not tell me it's done so.

It's always best to refer to the official documentation.

You can also use the FAQ.

In simple terms, it basically controls the type of SQL syntax MySQL should support. As far as what is "best", that depends on your specific needs. For example, lets say you are porting an existing Oracle application to MySQL. You could consider using the Oracle mode to help ease this transition.

In general, it's usually a good idea to use the default settings of the database unless you have good reason not to. When you start tweaking things and get into non-standard configurations, then that's one more thing you (or your DBA's) have to remember to do when you put your application into production. With that said, there are also plenty of reasons to fully exploit the features of your database. After all, with many RDBMS systems, you pay a lot of money for them, so why not use them fully?