Mrysql DVDs

introduction

In the previous article of this series, we examined the types of MySQL data to store the values ​​of history and time. However, even in the field of storing digital values, there are some unclearly undesirables. Even experienced developers can fall into the traps when it comes to a correct number flood ZEROFILL.

In this article, I will analyze the features of the digital data available in MySQL, including the correct species, fixed point, floating point and bitten types. As a reward, I will share the advice I found useful in choosing the correct type of job and avoid common risks.

Data types overview

Types of true number

MySQL supports five types of true number of different offers, all of which can be signed or uncompromising. These types are: TINYINTand SMALLINTand MEDIUMINTand INT ((INTEGER) And BIGINT. Here are some highlighting these types:

• All five support UNSIGNED option.
• View offer like INT(11) It no longer affects storage and is neglected.
• ZEROFILL Values ​​platforms with zeros and leading powers UNSIGNED; It was also neglected.

Types of fixed point

You He should Use DECIMAL(p,s) For fine digital values ​​such as currency and quantities, as each number specifically stores.

Types of floating separator

Use these for approximate digital values ​​where accurate accuracy is not important, like averages. You can use DOUBLE By default, or choose FLOAT If you agree on a smaller value or want to save memory and storage. Here are some highlighting these types:

• FLOAT/ /DOUBLE They are bilateral fractures. 0.1 It cannot be represented exactly, so expecting approximation.
• Use DOUBLE Unless the space or network is a decisive load.

Bit

This is the only special digital type, which aims to store a specific amount of bits. It can be used to store some “compressed” flags lists, such as access rights.

Other digital borrowed

• BOOL/ /BOOLEAN Foolish Sugar sentence construction to TINYINT(1).
• SERIAL It expands to BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

Tips and tricks

1. Choose the smallest correct number that fits, because it reduces indexes, cache, and backup.
2. Use UNSIGNED For naturally non -negative values ​​(IDS, charges), the positive range is doubled immediately.
3. money? He chooses DECIMALno FLOATAnd accountants hate fractures of the cinema.
4. Add CHECK Leading (MySQL 8.0+ Business): salary DECIMAL(9,2) CHECK (salary >= 0).
5. Strict SQL mode during development:
   SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO';
6. Planning billions of rows? Start with BIGINT UNSIGNED Or thought Uuid v7 To avoid early surplus.

summary

Choosing the correct digital type is half storage efficiency, and half the safety of the data. Learn about the cost of the house, and see the neglected features, and to rely on it DECIMAL or DOUBLE When necessary. Follow the strict advice to put and verify the above range, and you will avoid silent capacities, floods and fine traps that attract many production differences.