Select an appropriate data type for MySQL

1.1 Basic Principles for Data Type Selection

Prerequisites: Use a suitable storage engine

Selection Principle: Based on the selected storage engine, determine how to select the appropriate data type

The following options are classified by storage engine:

1.
MyISAM data storage engine and data column

For MyISAM data tables, it is best to use a fixed-length data column instead of a variable-length data column.

 

2. Memory storage engine and data column

Currently, memory tables use fixed-length data Row Storage. Therefore, it does not matter whether Char or varchar columns are used. Both are processed as char type.

3. InnoDB Storage engine and data column

We recommend that you use the varchar type.

For InnoDB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all rows use a header pointer to the value of the data column). Therefore, in essence, using a fixed-length char column is not necessarily easier than using a variable-length varchar column. Therefore, the main factor is the total amount of storage used by data rows. Because char occupies excessive space on average varchar, it is better to use varchar to minimize the total storage capacity of data rows to be processed and disk I/O.

 

 

1.2 fixed-length data columns and variable-length data Columns

3.2.1 char and varchar

A. Char and varchar types are similar, but they are stored and retrieved in different ways. Their maximum length is different from whether to add spaces at the end. Case-insensitive conversion is not performed during storage and retrieval.

The following shows the results of storing various string values in the char (4) and varchar (4) columns.

Value	Char (4)	Storage Requirements	Varchar (4)	Storage Requirements
''	' '	4 bytes	''	1 byte
'AB'	'AB '	4 bytes	'AB'	3 bytes
'Abcd'	'Abcd'	4 bytes	'Abcd'	5 bytes
'Abcdef'	'Abcd'	4 bytes	'Abcd'	5 bytes

Note: the value of the last row in the table above runs in SQL _mode = ANSI or an empty mode (non-strict mode)

If you run in strict mode, values that exceed the column length are not saved and an error occurs.

 

Mysql> Create Table VC (V varchar (4), c
Char (4 ));

Query OK, 0 rows affected (0.00 Sec)

 

Mysql> insert into VC values ('AB', 'AB
');

Query OK, 1 row affected (0.00 Sec)

 

Mysql> select * from VC;

+ ------ +

| V
| C |

+ ------ +

| AB
| AB |

+ ------ +

1 row in SET (0.00 Sec)

 

Mysql> select
Concat (v, '+'), Concat (C, '+') from VC;

+ --------------- +

| Concat (v, '+') | Concat (C, '+') |

+ --------------- +

| AB
+ | AB + |

+ --------------- +

1 row in SET (0.00 Sec)

The conclusion is: Char removes trailing spaces.

 

1.2.2 text and blob

When using text and blob field types, pay attention to the following points to make full use of the database performance:

1.
Blog and text values may also cause some problems, especially when a large number of Delete and update operations are performed.

Deleting such a value leaves a lot of fragments in the data table. The record length of these fragments may be different in the future. To improve performance, we recommend that you regularly use the optimize table function to fragment these tables.

 

2.
Use the merged index.

Composite Index columns are useful in some cases. One way is to create a hash value based on the content of other columns and store the value in a separate data column. Next, you can find the data row by searching the hash value.

However, this technique can only be used for exact match queries (hash values are useless for range search operators such as> or> = ).

3.
Avoid retrieving large blob or text values when unnecessary.

For example, a select * query is not a good idea and will transmit a large number of values on the network without any objective. This is also an example of storing BLOB or text identifiers in the composite index column.

4.
Separates blob or text columns from separate tables.

In some cases, if you move these data columns to the second data table, you can convert the data columns in the original data table to a fixed-length data row format, which makes sense. This reduces the fragmentation of the primary table and improves the performance of a data table with a fixed length of data rows. It will not transmit a large amount of data when running select * on the master table.

 

 

3.3 floating point number and fixed point number

Mysql> Create Table Test (C1
Float (10, 2), C2 decimal (10, 2 ));

Query OK, 0 rows affected (0.00 Sec)

Mysql> insert into test values (131072.32,
131072.32 );

Query OK, 1 row affected (0.00 Sec)

Mysql> select * from test;

+ ----------- +

| C1
| C2 |

+ ----------- +

|
13105.32 | 13105.32. |

| 131072.31/131072.32 |

+ ----------- +

2 rows in SET (0.00 Sec)

From the above we can see that 131072.32 is changed to 131072.31, which is caused by the inaccuracy of floating point numbers.

In MySQL, float, double, or real are floating-point numbers, and decimal are fixed points.

The advantage of a floating point over a fixed number of points is that a floating point can represent a larger data range with a certain length.

The disadvantage is that it will cause precision problems.

 

Therefore, pay attention to the following points when selecting:

1,
The floating point number has an error.

2,
Data that is sensitive to currency and other precision should be expressed or stored by a specific number of points.

3,
In programming, if floating point numbers are used, pay special attention to the error and try to avoid Floating Point Number comparison.

4,
Pay attention to the processing of some special values in floating point numbers.