Comprehensive understanding of data types in MySQL

1. Char and varchar types

char and varchar types are similar, both are used to store strings, but they save and retrieve strings in different ways. char belongs to the fixed-length character type , and varchar belongs to the variable-length character type. For example: for the two type definitions of char(4) and varchar(4):

(1), '' occupy 4 bytes in char(4), varchar(4) It only occupies one byte in length;

(2), 'ab' occupies 4 bytes in char(4), and only 3 bytes in varchar(4) Length;

(3), 'abcd' occupies 4 bytes in char(4), and 5 bytes in varchar(4);

Why is there an extra byte length in the varchar type? This is because the varchar type uses the extra byte to store the actual length of the varchar type. The retrieval of char(4) and varchar(4) is not always the same, for example:

mysql> create table char_and_varchar (v varchar(4),c char(4));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into char_and_varchar values ('ab  ','ab  ');
Query OK, 1 row affected (0.33 sec)

mysql> select concat(v,'cd'),concat(c,'cd') from char_and_varchar;
+----------------+----------------+
| concat(v,'cd') | concat(c,'cd') |
+----------------+----------------+
| ab  cd         | abcd           |
+----------------+----------------+
1 row in set (0.35 sec)

Since char is a fixed length, its processing speed is much faster than varchar , but its disadvantages are a waste of storage space, and the program needs to process trailing spaces. Therefore, data whose length does not change much and has high requirements for query speed can be considered to use the char type to store. As the MySQL version continues to upgrade, the performance of the varchar data type will also continue to improve, and the application scope of the varchar type will become wider.

In MySQL, different storage engines have different usage principles for char and varchar:

(1). In the MyISAM storage engine, it is recommended to use fixed-length field types instead of Variable length field type.
(2). In the Memory storage engine, fixed-length data lines are currently stored, so whether it is char or varchar type, it will be converted to char type for processing.
(3). In the InnoDB storage engine, it is recommended to use the varchar type.

2. TEXT and BLOB

When saving a small number of strings, you can use the char and varchar data types. When saving larger text, you usually choose to use text or BLOB. The main difference between the two is that BLOB can be used to save binary data, such as photos, while text can only be used to save character type data. Text and BLOB include three different types: text, mediumtext, longtext and blob, mediumblob and longblob respectively. The main difference between them is the length of text stored and the bytes stored.

Some issues that should be paid attention to when using BLOB and TEXT types:

(1) BLOB and TEXT will cause some performance problems, especially when a large number of deletion operations are performed. The deletion operation will leave large "holes" in the data table. Filling in these "holes" records in the future will have an impact on the insertion performance. In order to improve performance, you should regularly use the OPTIMIZETABLE function to defragment such tables to avoid holes causing performance problems.

(2) Use synthetic indexes to improve query performance for large text fields. The so-called synthetic index is to create a hash value based on the content of the large text field and store this value in a separate data column. Then the data row can be found through the hash value. For example:

mysql> create table t (id varchar(100),content blob,hash_value varchar(40));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values (1,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.33 sec)

mysql> insert into t values (2,repeat('beijing',2),md5(content)); 
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values (2,repeat('beijing 2008',2),md5(content));
Query OK, 1 row affected (0.01 sec)

mysql> select * from t;
+------+--------------------------+----------------------------------+
| id   | content                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 1    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijingbeijing           | 09746eef633dbbccb7997dfd795cff17 |
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
3 rows in set (0.00 sec)

mysql> select * from t where hash_value=md5(repeat('beijing 2008',2));
+------+--------------------------+----------------------------------+
| id   | content                  | hash_value                       |
+------+--------------------------+----------------------------------+
| 2    | beijing 2008beijing 2008 | 1c0ddb82cca9ed63e1cacbddd3f74082 |
+------+--------------------------+----------------------------------+
1 row in set (0.00 sec)

Synthetic index can only be used in exact matching scenarios, which reduces disk I/O to a certain extent and improves query efficiency. If you need to perform fuzzy queries on BLOB and CLOB fields, you can use MySQL's prefix index, that is, create an index for the first n columns of the field. For example:

mysql> create index idx_blob on t (content(100));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t \G
*************************** 1. row ***************************
        Table: t
   Non_unique: 1
     Key_name: idx_blob
 Seq_in_index: 1
  Column_name: content
    Collation: A
  Cardinality: 3
     Sub_part: 100
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql> desc select * from t where content like 'beijing%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
         type: ALL
possible_keys: idx_blob
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

(3). Do not retrieve large BLOB or TEXT fields unless necessary.

(4). Separate BLOB or TEXT fields into separate tables.

3. Floating-point numbers and fixed-point numbers

Floating-point numbers are generally used to represent values ​​that contain decimal parts. When a field is defined as a floating point type, if the precision of the inserted data exceeds the actual precision defined for the column, the inserted value will be rounded to the actual defined precision value. Then insert, the rounding process will not report an error. Float and double (real) in MySQL are used to represent floating point numbers.

Fixed-point numbers are different from floating-point numbers. Fixed-point numbers are actually stored in the form of strings, so fixed-point numbers can store data more accurately. If the precision of the inserted data is greater than the actual defined precision, MySQL will issue a warning, but the data will be rounded according to the actual precision before being inserted (if it is inserted in traditional mode, an error will be reported). In MySQL, decimal (or numerical) is used to represent fixed-point numbers.

Using floating point numbers to store data will cause errors. In scenarios with high accuracy requirements (such as currency), fixed point numbers should be used to store data. For example:

mysql> create table b (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.37 sec)

mysql> insert into b values (131072.32,131072.32);
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

四、日期类型

MySQL提供的常用的日期类型有：date、time、datetime、timestamp，日期类型的选用原则：

(1)、应根据实际需要选择能够满足应用的最小存储的日期类型；

(2)、如果要记录年月日时分秒，且年代比较久远，最好使用datetime类型；

(3)、如果记录的日期要被多时区的用户所使用，那么最好使用timestamp类型。

The above is the detailed content of Comprehensive understanding of data types in MySQL. For more information, please follow other related articles on the PHP Chinese website!