Data Types in MySQL

Source: Internet
Author: User
Tags time zones

Data Types in MySQL

I. char and varchar types

The char and varchar types are similar. They are used to store strings, but they are stored differently from the string retrieval method. Char is a fixed-length character type, and varchar is a variable-length character type. For example, for the char (4) and varchar (4) types:

(1) ''occupies 4 bytes in char (4), and varchar (4) occupies only one byte;

(2) 'AB' occupies 4 bytes in char (4), while varchar (4) occupies only 3 bytes;

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

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

12345678910111213 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)

Because char is fixed in length, its processing speed is much faster than that of varchar, but its disadvantage is that it wastes storage space and the program needs to process trailing spaces, therefore, the char type can be used to store more data with little length variation and high query speed requirements. As the MySQL version continues to upgrade, the performance of the varchar data type will also be improved, and the varchar type is more widely used.

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

(1) In the MyISAM storage engine, we recommend that you use a fixed-length field type instead of a variable-length field type.
(2) In the Memory storage engine, all data rows are stored with a fixed length. Therefore, both the char and varchar types are converted to the char type for processing.
(3) we recommend that you use the varchar type in the InnoDB Storage engine.

Ii. TEXT and BLOB

Char and varchar data types can be used to save a few strings. When saving large text, text or BLOB is usually used. 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 contain three different types: text, mediumtext, longtext, blob, mediumblob, and longblob. The main difference between them is that the length of the stored text is different from that of the stored byte.

Notes for using BLOB and TEXT types:

(1) BLOB and TEXT may cause some performance problems, especially when a large number of delete operations are performed. The delete operation will leave a large "empty" in the data table, and the insert performance will be affected if you fill in these "empty" records later. To improve performance, OPTIMIZETABLE should be regularly used to fragment such tables to avoid performance problems caused by holes.

(2) Use composite indexes to improve the query performance of large text segments. The so-called composite index is to create a hash value based on the content of a large text field, and store this value in a separate data column, then you can find the data row through the hash value. For example:

1234567891011121314151617181920212223242526272829 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)

Composite indexes can only be used in exact match scenarios, which reduces disk I/O to a certain extent and improves query efficiency. To perform fuzzy queries on BLOB and CLOB fields, you can use the MySQL prefix index to create indexes for the first n columns of the field. For example:

12345678910111213141516171819202122232425262728293031323334 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: ALLpossible_keys: idx_blob          key: NULL      key_len: NULL          ref: NULL         rows: 3        Extra: Using where1 row in set (0.00 sec)

(3) do not search large BLOB or TEXT fields.

(4) Separate BLOB or TEXT fields from a separate table.

Iii. Floating Point and fixed point

Floating Point Numbers are generally used to indicate the 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 in the column, the inserted value is rounded to the actually defined precision value, and then inserted, no error will be reported during rounding. Float and double (real) in MySQL are used to represent floating point numbers.

The number of fixed points is different from the floating point number. The number of fixed points is actually stored as strings, so the number of fixed points can store data more accurately. If the precision of the inserted data is greater than the actual precision, MySQL will issue an alarm, but the data will be inserted after rounding according to the actual precision (if it is inserted in traditional mode, an error will be reported ). In MySQL, decimal (or numberic) is used to represent the number of points.

Data stored with floating point numbers may have errors. In scenarios with high precision requirements (such as currency), you should use a fixed number of points to store data. For example:

12345678910111213 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)

Iv. date type

MySQL provides the following common date types: date, time, datetime, timestamp, and date type selection principles:

(1) Select the date type that can meet the minimum storage needs of the application;

(2) If you want to record the year, month, day, hour, minute, and second, and the age is relatively long, it is best to use the datetime type;

(3) If the recorded date is to be used by users in multiple time zones, it is best to use the timestamp type.

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.