Shulou(Shulou.com)06/02 Report--

Today, I will talk to you about the table building specifications in Mysql, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following contents for you. I hope you can get something according to this article.

1. Library names, table names and field names must be in lowercase letters and separated by "_". Library names, table names and field names must be no more than 12 characters. It is recommended to use nouns instead of verbs.

Second, it is recommended to use InnoDB storage engine.

Storage engine: innoDb supports things, myisam does not support things, it is recommended to use innoDb, default lead after 5.5, support transactions, row-level locks, better recovery, better performance under high concurrency, better support for hardware such as multi-core, large memory, ssd, etc.

(1) the MyISAM table is independent of the operating system, which means that it can be easily migrated from the Windows server to the Linux server; whenever we build a MyISAM engine table, we will create three files on the local disk, the file name is indicated. For example, if I set up a tb_Demo table for the MyISAM engine, the following three files are generated:

1.tb_demo.frm, storage table definition

2.tb_demo.MYD, storing data

3.tb_demo.MYI, store the index.

MyISAM tables cannot handle transactions, which means that tables with transaction requirements cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for the following situations:

1. Select a dense table. The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage.

two。 Insert intensive tables. MyISAM's concurrent insert feature allows you to select and insert data at the same time. For example, the MyISAM storage engine is ideal for managing mail or Web server log data.

(2) InnoDB is a robust transactional storage engine, which has been used by many Internet companies and provides a powerful solution for users to operate very large data storage. MySQL version 5.6.13 is installed on my computer, and InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, and InnoDB is the ideal choice in the following situations:

1. Update dense tables. The InnoDB storage engine is particularly suitable for handling multiple concurrent update requests.

two。 Business. The InnoDB storage engine is the standard MySQL storage engine that supports transactions.

3. Automatic disaster recovery. Unlike other storage engines, InnoDB tables can automatically recover from disasters. 4. Foreign key constraint. The only storage engine that MySQL supports foreign keys is InnoDB. 5. Support the automatic addition of column AUTO_INCREMENT attributes.

In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.

(3) MEMORY: the starting point for using the MySQL Memory storage engine is speed.

1. The target data is small and is accessed very frequently. Store data in memory, so it will cause the use of memory. You can control the size of the Memory table through the parameter max_heap_table_size. By setting this parameter, you can limit the maximum size of the Memory table.

two。 If the data is temporary and requires it to be available immediately, it can be stored in a memory table.

3. If the data stored in the Memory table is suddenly lost, it will not have a substantial negative impact on the application service.

(2) MERGE

The MERGE storage engine is a combination of MyISAM tables that must be identical in structure, and although their use is not as prominent as other engines, it is useful in some cases. To put it bluntly, the Merge table is the aggregator of several identical MyISAM tables; there is no data in the Merge table, and you can query, update, and delete tables of type Merge, which are actually operations on the internal MyISAM tables. Usage scenarios of Merge storage engine

Third, to store exact floating point numbers, you must use DECIMAL instead of FLOAT and DOUBLE.

(1) the decimal type can accurately represent very large or very precise decimals. Numbers up to 1028 (positive or negative) and up to 28 significant digits can be stored as decimal types without losing their accuracy. This type is useful for applications that must avoid rounding errors, such as accounting.

1. The decimal type is a 128bit data type suitable for financial and monetary calculations.

2. Decimal is not a floating point type, and decimal has no precision loss.

3. The amount that decimal can store is larger than that of double, and there will be no problem with the type conversion from double to decimal.

4 、

Float single precision floating point 32bit

Double double precision floating point 64bit

Decimal is a high-precision 128bit, floating point type.

Float double is the basic type (primitive type), decimal is not.

5, floating point operation will have the problem of precision loss, when there is precision loss, the program will not report, the programmer should pay attention to it.

(2) numeric types in mysql (excluding integers):

IEEE754 floating point numbers: float (single precision), double or real (double precision)

Fixed points: decimal or numeric

The effective digit binary of a single-precision floating-point number is 24 bits, 8 bits in decimal terms, and 53 bits in double-precision floating-point numbers, and 16 bits in decimal system

If the valid number of a real number is more than 8 digits, it will produce an error if it is expressed by a single-precision floating-point number. Similarly, if the significant number of a real number exceeds 16 digits, expressed as a double-precision floating-point number, there will also be an error.

(3) the IEEE754 standard computer floating-point number is internally expressed in binary, but it will also cause errors when converting a decimal number to a binary floating-point number, because not all numbers can be converted into binary numbers of finite length.

That is, a binary can be accurately converted to a decimal, but a decimal with a decimal may not be accurately represented in binary.

Fourth, it is recommended to use INT UNSIGNED to store IPV4.

(1) using UNSINGED INT to store IP addresses takes 4 bytes, while CHAR (15) takes up 15 bytes. In addition, the computer processes integer types faster than string types. Use INT UNSIGNED instead of CHAR (15) to store IPV4 addresses and convert them through the MySQL functions inet_ntoa and inet_aton. There is currently no conversion function for IPv6 addresses, and you need to use DECIMAL or two BIGINT to store them.

For example:

SELECT INET_ATON ('209.207.224.40')

3520061480

SELECT INET_NTOA (3520061480)

209.207.224.40

Fifth, do not add length to the shaping definition, such as using INT instead of INT (4).

(1) the maximum length of int data type in mysql is 11 digits and at least 4 digits, which is not enough to fill in the blanks.

(2) and in mysql, int itself is 4 bytes, bigint is 8 bytes, so the meaning of int (X) is that int determines the byte X of the data storage, which represents the column width of the desired data.

In the SQL statement, int represents the type of field you want to create, int represents the integer, and 11 represents the length of the field.

The display width of an integer column has nothing to do with how many characters mysql needs to display the column value, and has nothing to do with the amount of storage space required by the integer. For example, no matter how many characters the display width is set, bigint takes up 8 bytes.

Sixth, short data type, using TINYINT.

(1) with the same number of bytes, the range of non-negative storage values is larger. For example, the signed TINYINT is-128127and the unsigned is 0255.

1. TINYINT, field type. If set to UNSIGNED, only integers from 0 to 255 can be stored, not [negative].

2. Fields of TINYINT type will store integers from-128to 127if the UNSIGNED type is not set.

Tip: in general, in order to save space, you should use as little integer data as possible. A TINYINT data occupies only one byte, and an int data takes up four bytes. This may not seem like much difference, but in larger tables, the number of bytes increases rapidly. On the other hand, once you have created a field, it is difficult to modify it. Therefore, for security reasons, you should predict the maximum number of values that a field is likely to store, and then select the appropriate data type.

(2)

Tinyint 1 byte (- 128127)

Smallint 2 bytes (- 32768pm 32767)

Int unsigned 0-65535

Mediumint 3 bytes (- 8388608 instruction 8388607)

Int or integer 4 bytes (- 2147483648 min2147483647)

Seventh, it is not recommended to use the ENUM type, use TINYINT instead.

Explain in detail:

Speaking of this ENUM, after consulting the network abstracts of major technology communities, ENUM is indeed a characteristic field in mysql. I vaguely remember seeing it used in some well-known mall systems such as shopnc, but I didn't study it in detail, probably because it can set the range of fields, make the values can be controlled by the database, and have the function of enumerating constraints (for example, fields only want to have 0 and 1). If you use TINYINT (1), the result may appear 2, then 2 is stolen data)

However, ENUM also has some thorny problems. For example, when data is migrated, it is almost impossible for him to be supported by other databases. If there are strings in enum, it will be even more depressing for other databases, and it cannot be set to fields such as tinyint. (although enum can store strings, but internally, it is indexed in order, such as'a string, etc.' We can also use the index value to get the value select * from tbl_name whre enum = 2, which is equivalent to select * from tbl_name where enum ='b') if you see why these two sentences are synonymous with SQL, then you can also understand why the enum field is not advocated.

In other words, if an improperly designed ENUM field brings a complete nightmare to the programmer, for example, the scope of an enum field is ('0 'enum 1','2', 3', 4', 5'), while the index corresponding to the enumerated value of enum starts at 1, so insert into table (enum) field (1) inserts instead of 1, but 0.

In addition, if you design the enumerated field range of enum and use it for a period of time, add an enumerated value to the field range, and not add it at the end, then it is equivalent to changing the index value of the original range, that is, when you query the value directly (and add single quotation marks), you will not use the hidden index value of enum itself to get the results.

If it is a pure numerical type, it is recommended to use the tinyint field, after all, it only occupies one byte, even if there is stolen data, it can also be accepted, unlike enum, if the pure numeric range, change the index, you do not know whether the value you query is correct)

8. Avoid using TEXT and BLOB types as much as possible.

TEXT and BLOB types will slow down the query. If you need to save a very long character set, it is recommended to use the varchar (n) type or split the overly large fields into other tables.

Use VARBINARY to store variable-length strings and binary to store fixed-length strings. Because of the binary byte stream, there is no coding problem

Binary (n): fixed length is n bytes, where n values are from 1 to 8000, and the storage space is n bytes; varbinary (n | max): variable length, n ranges from 1 to 8, indicating that the maximum storage space is 2 ^ 31-1 bytes, that is, the maximum 4GB; n: if n is not specified in the table column definition or variable declaration statement, the default length is 1; if n is not specified in the CAST function, the default length is 30 Details:: [http://www.cnblogs.com/ljhdo/p/4530293.html](http://www.cnblogs.com/ljhdo/p/4530293.html)

Contrast

1. BLOB is a large binary object that can hold a variable amount of data. There are four types of BLOB: TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. They just differ in the maximum length of the allowable value.

There are four types of TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. These correspond to four BLOB types with the same maximum length and storage requirements.

2. The BLOB column is treated as a binary string (byte string). The TEXT column is treated as a non-binary string (character string). BLOB columns have no character set, and sort and compare numeric values based on column value bytes. The TEXT column has a character set, and values are sorted and compared according to the proofreading rules of the character set.

There is no case conversion during the storage or retrieval of TEXT or BLOB columns.

3. In most ways, you can think of a BLOB column as a VARBINARY column that can be large enough. Similarly, you can think of a TEXT column as a VARCHAR column. BLOB and TEXT differ from VARBINARY and VARCHA in the following aspects

Note:

Because the BLOB and text values can be very long, you may encounter some constraints when using them:

Only the first max_sort_length bytes of the column are used when sorting. The default value for max_sort_length is 1024; this value can be changed using the-- max_sort_length option when the d server is started.

Increasing the value of max_sort_length at run time makes more bytes meaningful when sorting or combining. Any client can change the value of its session max_sort_length variable:

For a long time, it is forbidden to use VARBINARY or BLOB in the database to store pictures, files, etc.

It is more efficient to use a distributed file system if you want to store pictures, files, etc.

Ten, VARCHAR (N), N represents the number of characters rather than bytes, such as VARCHAR (255), can store up to 255Chinese characters, need to choose N according to the actual width.

Difference:

1. Summary of char:

The maximum length of char is 255 characters. Note that the number of characters has nothing to do with the character set. There can be default values, and trailing spaces will be truncated.

2. Summary of varchar:

The maximum length of varchar is 65535, which refers to the number of bytes that can be stored. In fact, it can only store up to 65532 bytes, with 3 bytes left for storage length. Note that the number of bytes has something to do with the character set. A Chinese character occupies 3 bytes with utf8 and 2 bytes with gbk. The maximum number of characters stored varies depending on the encoding, usually nasty 65532 Universe 3 or nasty 65532 Universe 2 characters. You can have default values, and trailing spaces will not be truncated.

Second, theoretical knowledge

Let's first explain the definition of varchar in the historical version of MySQL:

Below version 4.0, varchar (50) refers to 50 bytes. If you store UTF8 characters, you can only store 16 characters (3 bytes each).

Version 5.0 and above, varchar (50), refers to 50 characters, which can be stored in either numeric, alphabetic or UTF8 Chinese (3 bytes each).

Storage limit

Extra bytes are required to store the length of characters: 1 byte for less than 255 and 2 bytes for greater than 255

Coding limit

Gbk: up to 2 bytes per character

Utf8: up to 3 bytes per character

Utf8mb4 takes up to 4 bytes per character, Chinese 3 bytes, and emoji emoticons 4 bytes.

Column length limit

MySQL defines that the length of rows cannot exceed 65535, which limits the number of columns. For example, all columns are char (128) utf8 character set, with a maximum of 65535 / (128 / 3) = 170 columns.

First, select UTF8 for the table character set.

(1) use the utf8 character set. If it is a Chinese character, it takes up 3 bytes, but the ASCII character is still 1 byte.

(2) uniform, there will be no risk of garbled code in conversion.

(3) users in other regions (the United States, India, Taiwan) can read your text normally without installing simplified Chinese support, and there will be no garbled code.

(4) ISO-8859-1 encoding (latin1) uses all the space in a single byte, and any byte stream that transmits and stores any other encoding in a system that supports ISO-8859-1 will not be discarded. That is to say, it is no problem to treat any other encoded byte stream as an ISO-8859-1 code, and the byte stream is preserved intact.

YEAR type is used in storage year.

* * key:: date and time type of mysql: divided into time, date, datetime, timestamp, year,**

(1) Type support: year and year (4), note that there is no definition of year (2), otherwise error "[Err] 1818-Supports only YEAR or YEAR (4) column."

Create table if not exists time (

The definition of atime YEAR # year can be written as year or year (4)

) engine = innodb charset = utf8

(2), insert values, support integers and strings, and support 2 or 4 digits

00000069 will be converted to 2000002069

7099 will be converted to between 1970 and 1999

# Test year type insert into time values (78); # display in database: 1978

Insert into time values ('78'); # display in the database: 1978

Insert into time values ('1978'); # the database shows: 1978

(3) pay attention.

1. The number 0 or the string 0 can be inserted. The actual value displayed is different.

Insert into time values (0); # display in the database: 0

Insert into time values ('0'); # the database shows: 2000

2. Year only saves the year and takes up a small amount of space

3. Other date-related items can be saved by integer

At the beginning of time: deposit 9 digits

Thirteen, storage date using DATE type.

MySQL date type, date format, storage space, date range comparison.

Date type storage space date format date range

Datetime 8 bytes YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 to 9999-12-31 23:59:59

Timestamp 4 bytes YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 ~ 2038

Date 3 bytes YYYY-MM-DD 1000-01-01-9999-12-31

Year 1 bytes YYYY 1901 ~ 2155

Storage time (accurate to seconds) it is recommended to use the int/bigint type, 4 bytes for int and 8 bytes for bigint.

1) int (1) 4-byte storage, INT is 4 bytes long, less storage space than datatime, int index storage space is also relatively small, sorting and query efficiency is relatively high. (2) the readability is very poor, unable to see the data intuitively, which may annoy you.

You can pay a little attention to the pitfalls of the 2038 problem. For MySQL, use timestamp or bigint instead of int if you save the timestamp. 2) TIMESTAMP

(1) 4 bytes are stored. (2) the value is saved in UTC format. (3) the time zone is converted, the current time zone is converted during storage, and then converted back to the current time zone during retrieval. (4) the TIMESTAMP value cannot be earlier than 1970 or later than 2037.

3) datetime (1) 8 bytes store (2) regardless of time zone (3) retrieve and display datatome values in 'YYYY-MM-DD HH:MM:SS' format. The range of support is' 1000-01-01 00 0000'to '9999-12-31 23 59

15. The recommended field is defined as NOT NULL.

* * (1) * * Null values do not take up space, and not null is more efficient than null.

* * (2) * * NULL in MySQL actually takes up space: for example, you have a cup, a null value indicates that the cup is vacuum, and NULL represents a cup filled with air, although the cup looks empty, but there is a big difference.

Sixteen. Table structure changes need to be notified to DBA for review.

Database administrator DBA: Database Administrator

* * (1) * * every change cannot be said to change, otherwise, if others do not know, it must be in accordance with the original, and if you report an error, it will not be easy to move on.

After reading the above, do you have any further understanding of the table building specifications in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.