What are the data types of mysql?

3. Usage Suggestions

1. When specifying the data type, the principle of small size is generally adopted. For example, if you can use TINY INT, it is best not to use INT, and if you can use FLOAT type, it is best not to use DOUBLE type, so It will greatly improve the operating efficiency of MYSQL, especially under large data volume testing conditions.

2. There is no need to design the data table too complicated. The distinction between functional modules may be more convenient for later maintenance. Be careful when presenting a hodgepodge of data tables.

3. The classification of data tables and fields Naming is also a skill

4. Before designing the data table structure, please imagine it is your room. Maybe the result will be more reasonable and efficient

5. The final design result of the database must be It is a trade-off between efficiency and scalability. It is inappropriate to favor either side.

Basic principles for selecting data types

Prerequisite: Use a suitable storage engine.

Selection principle: Determine how to choose the appropriate data type based on the selected storage engine.

The following selection methods are classified by storage engine:

• MyISAM data storage engine and data columns: MyISAM data table, it is best to use fixed-length (CHAR) data columns instead of variable Data column of length (VARCHAR).
• MEMORY storage engine and data columns: MEMORY data tables currently use fixed-length data row storage, so it does not matter whether you use CHAR or VARCHAR columns. Both are handled as CHAR types.

• InnoDB storage engine and data columns: It is recommended to use VARCHAR type.

For InnoDB data tables, the internal row storage format does not distinguish between fixed-length and variable-length columns (all data rows use head pointers pointing to data column values), so in essence , using fixed-length CHAR columns is not necessarily simpler than using variable-length VARCHAR columns. Therefore, the main performance factor is the total storage used by the data rows. Since CHAR takes up more space on average than VARCHAR, it is better to use VARCHAR to minimize the total storage and disk I/O of data rows that need to be processed.

Let’s talk about fixed-length data columns and variable-length data columns.

char is similar to varchar

The CHAR and VARCHAR types are similar, but they are saved and retrieved differently. They also differ in terms of their maximum length and whether trailing spaces are preserved. No case conversion is performed during storage or retrieval.

The following table shows the results of saving various string values ​​into CHAR(4) and VARCHAR(4) columns, illustrating the difference between CHAR and VARCHAR:

Please note that the value in the last row in the above table only applies when strict mode is not used ; if MySQL is running in strict mode, the value that exceeds the column length will not be saved , and an error will occur.

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)
 
mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)
 
mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab  +          | ab+            |
+----------------+----------------+
1 row in set (0.00 sec)

②Use synthetic (synthetic) index. Synthetic index columns are useful sometimes. One way is to create a hash value based on the contents of other columns and store this value in a separate data column. You can then find the row of data by retrieving the hash value. However, we should note that this technique can only be used for exact match queries (hash values ​​are not useful for range search operators like =). We can use the MD5() function to generate the hash value, we can use SHA1() or CRC32(), or we can use our own application logic to calculate the hash value. Remember that numeric hash values ​​can be stored very efficiently. Likewise, if a hashing algorithm produces strings with trailing spaces, do not store them in CHAR or VARCHAR columns; they will be affected by trailing space removal.

Synthetic hash indexes are particularly useful for those BLOB or TEXT data columns. A search using a hashed identifier value is much faster than searching the BLOB column itself.

③Avoid retrieving large BLOB or TEXT values ​​when unnecessary. For example, a SELECT * query is not a good idea unless you can be sure that the WHERE clause as a constraint will only find the required rows of data. Otherwise, you could transmit a large number of values ​​over the network without any purpose. This is also an example where it helps us to have BLOB or TEXT identifier information stored in a synthetic index column. You can search the index column to determine which data rows are needed, and then retrieve the BLOB or TEXT value from the qualifying data rows.

④ Separate BLOB or TEXT columns into separate tables. In some environments, it may make sense to move these data columns to a second data table if it allows you to convert the data columns in the original data table to a fixed-length row format. This reduces fragmentation in the main table and gives you the performance benefits of fixed-length data rows. It also allows you to run SELECT * queries on the main data table without transmitting large amounts of BLOB or TEXT values ​​over the network.

mysql> CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));
Query OK, 0 rows affected (0.29 sec)
mysql> insert into test values(131072.32,131072.32);
Query OK, 1 row affected (0.07 sec)
mysql> select * from test;
+-----------+-----------+
| c1        | c2        |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

Floating point numbers have error problems;
1. is sensitive to precision in currencies, etc. The data should be represented or stored with fixed-point numbers;
2. In programming, if floating-point numbers are used, special attention should be paid to error issues and try to avoid floating-point comparisons;
3. Be careful with floating-point numbers Handling of some special values.