MYSQL data type

The MySQL database has a very high number of data types, and choosing the right data type is critical to gaining performance. Data is stored and stored by the engine, and different storage engines may differ in how they store the same type of data.

Generally grasp the following points:

1. Smaller is usually better

   1. In general, you should use the smallest data type that correctly stores your data when possible. Smaller data types are usually faster.

2. It's simple.

   1. Simple data type operations typically require less CPU cycles.

3. Try to avoid using null, which is generally preferable to specifying column NOT NULL.

   1. Nullable columns make indexing, index statistics, and value comparisons more complex.

   2. Nullable columns use more storage space and do special processing in MySQL.

Data type Analysis

1. Integer

• 
  

  • MySQL provides several types of integers: TINYINT, SMALLINT, Mediumint, INT, BIGINT. Use 8, 16, 24, 32, 64-bit storage space respectively.

  • An integer type has an optional unsigned property that indicates that negative values are not allowed (this can widen the range of positive numbers).

  • For storage and computation, int (11) and Int (20) are the same, but specify the number of characters that some of MySQL's interactive tools use to display.

1. Real

• MySQL provides several types of real numbers: FLOAT, DOUBLE, DECIMAL. FLOAT, Double uses 32, 64-bit storage space respectively. Decimal allows a maximum of 65 digits in MySQL 5.0 and later versions.

• Decimal can hold a larger number than bigint, and is generally used only when the mouse is accurately calculated.

String type

• MySQL provides several string types: VARCHAR, CHAR. varchar is used to store variable-length strings.

• VARCHAR is more space-saving than a fixed-length string because it uses only the necessary space, so it is also useful for performance. However, the update needs to be longer than the original, which results in additional work.

• VARCHAR is best suited for string columns with a maximum length greater than the average length, and fewer updates for columns.

• The char type is fixed-length. MySQL allocates enough space in which to define the length of the string.

• Storing the char value is, MySQL deletes all the trailing spaces.

• Char is suitable for storing very short strings, all of which are near a length of data, such as MD5 values.

• It is unwise to be generous. The space overhead of varchar (5) and varchar (200) storing ' hello ' is the same. However, longer columns consume more memory .

Bolb and text types

• Both Bolb and text are string data types that are designed to hold big data. stored in binary and character mode, respectively.

• The character types are: Tinytext, Smalltext, TEXT, Mediumtext, longtext, corresponding binary types are: Tinyblob, Smallblob, BLOB, Mediumblob, Longblob. BLOBs are synonyms of smallblob, and text is synonymous with smalltext.

Using enumerations instead of string types

• Enumeration columns can sometimes be used instead of commonly used string types. It's not dramatic. You can store some of the non-repeating strings as a predefined collection.

Date and Time type

• DateTime is stored when the ANSI standard defines the date and time representation method, exactly to the second.

• Timstamp saves the number of seconds since midnight January 1, 1970, which is the same as the Unix timestamp.

• In addition to special behavior, you should always use timestamp as much as possible, as it is more efficient than datetime space.

This article is from the "bottom Small tall" blog, please be sure to keep this source http://3157689.blog.51cto.com/3147689/1697394

MYSQL data type