MySQL data type

First, numeric type

1. For integer data, MySQL supports specifying the display width within the parentheses following the type name, or int (11) If not specified, and generally with zerofill, with insufficient number of digits

Fill with the character "0".

Field Definition Example:phone int zerofill;

2. If a column is specified as zerofill, Mysql automatically adds the unsigned property for that column ...

3. A table can have a maximum of one auto_increment column.

4. fixed-point decimal is stored as a string inside Mysql and is more accurate than a floating-point number. (the default integer digit is 0, and the decimal place is the number). There are errors in floating-point data storage, and the comparison of floating- point numbers is also

error, should be avoided, if you want to use a floating-point comparison, it is best to use the range comparison and do not make Compare with "= =".

5. Bit type is used to hold bit field values. For bit fields, queries using the Select command directly will not see the results, and can be read using the bin () or the hex () function. Display them as binary and 16, respectively

binary format. If the number of digits exceeds the bit Type field length, it cannot be inserted.

6. Floating-point numbers when the precision of the inserted data exceeds the field precision definition, the data is rounded and inserted, and the floating-point number of the process does not cause an error, but the fixed points may be warned and inserted, or straight

error, cannot be inserted, it depends on Sqlmode.

Ii. Date and Time type

1. Mysql only sets the default value for the first TIMESTAMP field in the table to the system time Current_timestamp, if there is a second TIMESTAMP Type field, the default value is set to 0.

2. TIMESTAMP is also related to the time zone, when the date is inserted, it will be converted to the local time zone after the storage, and when taken out from the database, also need to convert the date to the local time zone after the display. This way, two

users in different time zones may see the same date differently.

3. Mysql has a valid range of values for each datetime type, and if this range is exceeded, the system will prompt for errors under the default Sqlmode and will be stored with a value of 0.

4. The TIMESTAMP value range is from 1970 year to 2038 year, so it is not suitable for storing older dates.

5. TIMESTAMP 's properties are greatly affected by the Mysql version and server sqlmode .

6. Usage Guidelines for datetime types: Select the date type that meets the minimum storage for your app.

Attention:

the time type of Mysql cannot be assigned a timestamp ... Instead, you should press YYYYMMDDHHMMSS in such a time format (you can add a number of different separators between months and seconds), or use the Mysql now () the function is assigned a value.

Three, string type

1. at the time of retrieval,thechar column removes spaces at the trailing end of the string, while varchar retains the spaces. binary and varbinary are similar to char and varchar, The difference is that they contain binary strings

instead of including non-binary strings. When a binary value is saved, at the end of the value by padding 0 bytes to reach the specified field definition length, such as: insert 'a ' in a field defined as binary (3) ',

make with Hex function View Yes 610000.

2. Since char is fixed-length, it is much faster to process than varchar , but its disadvantage is the cost of storage space, the program needs to deal with the line trailing space. For those whose lengths vary little and are

data with higher query speeds can be considered for storage using the char type.

3. in MySQL , different storage engines use the same principles for CHAR and VARCHAR :

(1)MyISAM Storage Engine: It is recommended to use a fixed-length data column instead of a variable-length data column.

(2)memory Storage Engine: Currently uses fixed-length data row storage, so whether CHAR or VARCHAR is used columns are not related. Both are treated as CHAR types.

(3) InnoDB storage Engine: It is recommended to use VARCHAR type. For InnoDB data tables, the internal row storage format does not differentiate between fixed-length and variable-length columns (all data rows use point-to-data

The header pointer of the column value), so in essence, use a fixed-length CHAR columns do not necessarily have better performance than using variable - length VARCHAR columns. Thus, the main performance factor is the total storage of data rows used

volume. Because CHAR takes up more space than varchar on average , varchar is used to minimize the amount of storage and disk for the data rows that need to be processed I/O is relatively good.

4. The main difference between text and blob is that blobs can be used to store binary data such as photos, while text can only Save character data, such as an article or journal.

5. Blob and text values can cause some performance problems, especially when a large number of deletions are performed, leaving a large "void" in the table, and the performance of inserting these "empty" records into

have an impact. To improve performance, it is recommended that you periodically defragment such tables using the Optimize table feature to avoid the "holes" that cause performance problems.

It can also be seen that:after the MySQL data table deleted records, the data table file size will not be reduced, the record deleted place will leave some blanks, and then insert the record will be the first to fill the empty

White. Of course, these blanks can be removed manually using the Optimize table command, making the data file smaller.

6. You can use a composite index to improve query performance for large text fields:

In a nutshell, a composite index creates a hash value based on the contents of a large text field (for example, using the MD5 function) and stores the hash value in a separate data column, which can then be retrieved

The hash value finds the data row. This reduces I/O to a certain extent, thus improving query efficiency. However, this technique can only be used for exact matching queries, such as the < or >= range Search operators are not

the use of. If you need to make a fuzzy query on a large text field, you can create an index for only the first n characters of a field , for example:

Create index Idx_blob on t (content), where content is the text Type field. This makes it possible to use the prefix index for fuzzy queries on the first four characters of the content, but

The "%" in the query condition cannot be placed first, otherwise the index will not be used.

7. The ENUM type is case-insensitive. When inserting a value that does not exist within the specified scope of an enum, there is no return warning (Mysql5.7 insert failed), but an enum is inserted The first value in theenum

type only allows you to select a single value from a collection of values.

8. compared to ENUM,set can store fewer members and require more bytes, but it is set You can select more than one member at a time. Values that exceed the allowable range of values are not allowed to be inserted, for

A collection that contains duplicate members will take only one.

MySQL data type