MySQL Design specification

First, the table name should not be mixed with case (that is, camel), you should use an underscore as a connector.

Because in thinkphp, the M (' Table name ') method, the case of this form of table name will automatically be converted into an underscore form, resulting in unreadable, can only use the M () empty method to operate the database, it will bring a lot of inconvenience to the development of the later.

second, pay attention to SQL keyword very much ,like (likes number, SQL keyword is fuzzy query), Order(order, SQL keyword is sort), etc. these are likely to be misused and result in some SQL statements can not be executed. Avoid design-time, including table name, column name.

Third, how to make the MySQL where query condition is case-sensitive:

Root cause: MySQL's general database encoding format is UTF8,UTF8_GENERAL_CI.

One of the CI is case insensitive meaning, that is, casing is not sensitive! Therefore, the conditions used when querying are case-insensitive.

Like what:

SELECT *  from WHERE Name='batsing'

will be named Batsing and Batsing, and so on.

Solution 1: Add collate utf8_bin at the end of the query statement, that is, specify that the statement is case-sensitive. But some will error, you need to debug more. Such as:

SELECT *  from WHERE Name='batsing' collate utf8_bin

Solution 2: Modify the database type, you can modify the entire database, or you can modify a single table, this depends on your own project to decide.

For example, change to utf8_bin encoding format.

MySQL Design specification