Know Your MYSQL: Do's and Don'ts

We are starting with the MySql engines: There are many types of the MySql table engines, which are used to to defines the table data structure. Normally, you should Do use InnoDB; don't use MyISAM. 

Unless you have to implement the full-text search in the large data table, you should prefer the InnoDB otherwise for the full-text, the MyISAM is the best idea. the full-text matching does not available in InnoDB. 

The combination of the table will the optimized idea. Push the regular data in the InnoDB and the searchable data in MyISAM

Do use BINARY columns when you want rigorous string matching, otherwise you get a case-insensitive comparison by default. Do set the collation correctly for your character set (best: UTF-8) or case-insensitive comparisons will behave strangely.

Do use ANSI SQL mode if you want your code to be portable. ANSI_QUOTES allows you to use standard double-quoted "identifier" (table, column, etc.) names to avoid reserved words; MySQL's default way of saying this is backquotes but they're non-standard and won't work elsewhere. If you can't control settings like this, omit any identifier quoting and try to avoid reserved words (which is annoying, as across the different databases there are many).

Do use your data access layer's MySQL string literal escaping or query parameterisation functions; don't try to create escaped literals yourself because the rules for them are a lot more complicated than you think and if you get it wrong you've got an SQL injection hole.

Don't rely on MySQL's behaviour of returning a particular row when you select columns that don't have a functional dependency on the GROUP BY column(s). This is an error in other databases and can easily hide bugs that will only pop up when the internal storage in the database changes, causing a different row to be returned.