MySQL data type

The so-called data type: A uniform classification of data. From the system point of view, is to use a unified approach to management, better use of limited space.

There are three main categories of data types in sql: numeric type, String type, and time date type

Numeric type

Numeric data: All values
The system divides the numeric type into integers and decimal types

Integer type

Store shaping data: In SQL because more to consider how to save disk control, so the system will subdivide the integer type 5
1. Tinyint: Mini integer with one byte storage, representing a maximum of 256 states. Common
2. Smallint: Small integer with 2 byte storage, representing a state of up to 65536.
3. Mediumint: Medium integer with 3 byte storage
4. Int: Standard integer with 4 byte storage (common)
5. Bigint: Large integer with 8 bytes of storage

Create an integer table

Insert data: Smart Insert int, can only insert data in range

All numeric types in SQL are signed by default: positive or negative
Sometimes you need to use unsigned data: You need to qualify the data type: int unsigned

Insertion of numeric values

When looking at the table structure, we find that the data type of each field is followed by a parenthesis with the specified number representing the width of the display.

Display width: No special meaning, but the default is to tell the user can display the form, in fact, the user can be controlled, this control does not change the size of the data itself.

The meaning of the display width: When the data is not enough to display the width, the data will be automatically programmed to the corresponding display width. It is usually necessary to add a width with a leading limit, without changing the value, Zerofill (0 padding), and 0 padding will cause the value to automatically become unsigned.

0 Fill + display width effect:

0 Meaning of fill (display width): Guaranteed data format

Decimal type

Decimal type: A numeric type with a decimal point or range beyond an integer type.
In sql: Divide the decimal type into two types: floating-point and fixed-point
Floating point: floating point, limited precision, and loss of precision
Fixed-point type: decimal point fixation, precision fixed, no loss of precision

Floating point Type

Floating-point data is a precision data, because the precision is lost (auto-rounding) after the specified range is exceeded
Floating point type: Theory is divided into two kinds of precision
1. Float: Single precision, 4 bytes of storage data, accuracy range of about 7 bits
2. Double: doubles, takes 8 bytes to store data, the accuracy range is about 15 bits

Create a floating-point list: Floating point usage, direct float means no fractional part, float (m,d): M for total length, D for fractional part length, integer part length to m-d

Insert data: Can be a direct decimal, or it can be a scientific notation

Insertion of floating-point data: Integer part cannot exceed length, but fractional part can exceed length (system will automatically round up)

Result: Floating-point numbers are bound to be rounded and floating-point numbers are allowed if the integer part exceeds the specified length because of system rounding.

Fixed-point type

Fixed-point type: Absolute guarantee that the integer part will not be rounded, the fractional part is possible (theoretically no loss of precision)

Create a fixed-point data table: Comparing floating-point numbers

Insert data: The integer portion of the fixed-point number must not exceed the length, the length of the fractional part can be arbitrarily exceeded (rounded)

Floating-point number if the carry causes the length to exceed no problem, but the fixed-point count is not.

View data:

= = = Gorgeous Split line = = = Time Date type

DateTime: Time date, format Yyyy-mm-dd HH:ii:ss, indicating range is from 1000-9,999, has 0 value 0000-00-00 00::00:00
Data: Date, is the date part of datetime
Time: Duration (segment), between the specified touch intervals
Timestamp: timestamps, not timestamps, but yyyy-mm-dd from the beginning of the 1970 HH:II:SS
Year: Years, two forms, annual (2) and yearly (4)

Create Time Date Table

Insert data: Time can be negative, and can be a large negative number, year can be inserted with 2 digits, or 4 digits can be used

Timestamp field: This field will automatically update as long as the current record is updated

String type

In SQL, the string types are divided into 6 classes: Char,varchar,text,blob,enum,set

Fixed-length string:

Fixed length string: Char. Disk (two-dimensional table) when defining the structure, the storage length of the final data has been determined

CHAR (L): L indicates the length that can be stored, in characters, the maximum length can be 255
CHAR (4) under UTF8, 12 bytes required

Variable length string:

Variable length string: varchar. When allocating space, according to the maximum space allocation, but in fact, the final use of how much, based on the specific data to determine.

varchar (l), the theoretical length of L is 65,536 characters, but it will be one or two more bytes to determine the actual length of the storage.
varchar (10) Does save 10 characters, UTF8 10*3 + 1 =31 (bytes)
Store 3 Kanji, 3 * 3 +1 =10 (bytes)

How to choose fixed length or variable length string?
Fixed-length disk space is wasteful, but highly efficient. such as ID card, mobile phone number, with fixed length
Longer disk space is more economical, but less efficient. For example, name, address, with variable length.

Text string

If the amount of data is very large, usually more than 255 characters, a text string is used.

Text strings are divided into text and blobs based on the format of the stored data
Text: Storing text (binary data is actually a storage path)
Blob: Storing binary data (usually not used)

Enumeration string

Enum: enum, which designs all possible results beforehand, actually stores the data that must be one of the well-defined data.

How enumerations are used
Define the list of elements that an enum may appear in://such as: Enum (' Male ', ' female ', ' no male no female ', ' demon ', ' secret ');
Use: To store data, only the data defined above can be stored.

Creating an enumeration table

Join data: One of the functions, canonical data format

Role two: Save storage space: The enumeration actually stores the numeric value, not the string itself.

Find out the regularity of the enumeration elements: numbering from the beginning, in the order in which the elements appear.

Enumeration principle: Enumeration in the data specification, the system will automatically establish a number and enumeration elements of the corresponding relationship (the relationship is put into the log), and then when the data is inserted, the system automatically converts the characters to the corresponding digital storage, and then when the data extraction, The system automatically converts the value to the corresponding string display.

Because the enumeration actually stores numeric values, you can insert the numbers directly

Collection string

Collections are similar to enumerations: they are actually stored as numeric values, not strings (the collection is multi-selected)

How to use Collections:
Definition: Set (element list)
Use: You can use more than one element in the list, using commas to split

Insert data: You can use multiple element string combinations, or you can insert numeric values directly.

View data: Value + Data View

Each element in the collection corresponds to a corresponding bits

The order of the elements in the collection is not related, and the final system will go to match order.

The power of collections is the ability to standardize data and save space

Mysql record length

MySQL stipulates that no record can be longer than 65,535 bytes (varchar will never reach the theoretical value)
How much can the actual storage length of varchar reach? See Character Set encoding
UTF8 the actual top with of the varchar: 21844
GBK the actual top with of the varchar: 32766

To use the entire 65,535 byte length, add a tinyint field (not null)

Text text string in MySQL, does not occupy record length: Extra storage, but text text string is also part of the record, must occupy a portion of the length of the Record: 10 bytes (the address and length of the data to be saved)

MySQL data type