MySQL Database Build table considerations

1. Library name, table name, field name must use lowercase letters, "_" split.

Reason:

MySQL under Linux database name, table name, column name, alias casing rules are like this:
1.Database nameAndTable nameIs strictly case-sensitive;
2.Alias of TableIs strictly case-sensitive;
3.alias for column name and columnIn all cases, it isIgnore CaseOf
4.Variable nameis also strictly case-sensitive;
MySQL is case insensitive under Windows. 
So in different operating systems in order to make the program and database can work properly, the best way is to be in the design of the time to lowercase, but if the design has been normalized case, then in the Windows environment as long as the configuration of the database to make changes to the line, the specific operation is as follows:
Add a line in the MySQL configuration file My.ini [mysqld] lower_case_table_names = 1
Parameter explanation:
0: Case Sensitive
1: Case insensitive
In MySQL, the database and table pairs are in the directories and files under those directories. Thus, the sensitivity of the operating system determines the case sensitivity of database and table naming. This means that the database and table names are case-insensitive in Windows and are case-sensitive in most types of UNIX systems. Oddly, the alias of the column name and column is case-insensitive in all cases, and the table alias is case-sensitive.
To avoid this problem, you might want to use a combination of lowercase letters and underscores instead of any uppercase letters when defining database naming rules.
Alternatively, you can force the start of mysqld with the-o lower_case_table_names=1 parameter (if you use the--DEFAULTS-FILE=...\MY.CNF parameter to read the specified configuration file to start mysqld, you need to configure the [ MYSQLD] section to add a row of Lower_case_table_names=1). So MySQL
All table names are automatically converted to lowercase characters when you create and find them (this option defaults to 1 in Windows and 0 in Unix.) Starting with MySQL 4.0.2, this option also applies to the database name.

2. It is recommended to use the InnoDB storage engine.

Reason:

1.MyISAM

MyISAM is the default engine for mysql5.1 and previous versions and has not been updated for a long time.

With table-level locks, if the amount of data is large, an insert operation locks the table and other requests are blocked.

Support Full-Text indexing

Support for query caching

Saves the total number of rows in the table, uses count (*), and does not have a WHERE clause to return the results very quickly, but does not have a WHERE clause when actually used

Brief: Transaction security is not supported

There is no transaction log and classification, so it only writes data to the Linux file cache and wants to eventually write to disk. If the system crashes or loses some data during this process, the MyISAM table often fails to start or warns you of the need to repair the table; It has limited methods of recovering data and often loses data. In addition, the MyISAM is difficult to properly backup, the time to backup usually need to lock the entire system of data, which means that every day the site will be down or not used for a period of time.

The MyISAM table allows full-text indexing in a recurring column, and InnoDB does not support it (this is not understood)

2.InnoDB

Support Transactions

Support for row-level and table-level locks to support more concurrent volumes

mysql5.6 version starts to support full-text indexing

Query does not lock, completely does not affect the query

InnoDB sets a transaction counter for each table that stores the current maximum transaction ID. When a transaction commits, InnoDB uses the transaction ID of the system transaction ID in MVCC with the new current table counter. Only transactions larger than this maximum ID can use the query cache. Other transactions that are smaller than this ID cannot use the query cache. Also, in InnoDB, all transactions that have lock operations do not use any query cache

Brief: Support for transactional security

The transaction log records true database transactions, but more importantly, data crash recovery and rollback. INOODB-based IO provides more secure data protection and better performance

The old bull by the river
Links: HTTP://WWW.JIANSHU.COM/P/6B45B150BFBF
Source: Pinterest
Copyright belongs to the author. Commercial reprint please contact the author for authorization, non-commercial reprint please specify the source. 3. Storage precision floating-point numbers you must use decimal instead of float and double.

4. We recommend using unsigned to store non-negative values.

5. It is recommended to use the int unsigned storage IPV4. Reason:use intunsigned instead of char (15) to store IPv4 addresses, and convert through MySQL functions Inet_ntoa and Inet_aton; Ipv6 address currently has no conversion function and requires a decimal or two bigint to store. For example:

CREATE TABLE T (

IP INT UNSIGNED

);
Insert INTO T select Inet_aton (' 209.207.224.40 ');
Select Inet_ntoa (IP) from T;

6. No length is added to the shaping definition, such as int, not int (4).

Length is independent of the size of the number of numeric values you store (refer to http://www.cnblogs.com/echo-something/archive/2012/08/26/mysql_int.html)

MySQL Database Build table considerations