MySQL basic note (vii)-data type two

String type

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

　　char, fixed-length string

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

CHAR (L): l represents length, which can be stored in characters. The maximum length value is 255.

　varchar variable-length string

　　When allocating space, according to the maximum space allocation, but in fact, the final amount of space used, is based on specific data to determine.

varchar (l): l represents the length of a character, the theoretical length is 65,536 characters, but 1 to 2 bytes are added to determine the actual length of the storage.

For example, varchar (10): 10 characters are stored, UTF8, 10*3+1=31 bytes.

Storage of 3 kanji, 3*3+1 = ten (bytes)

How to choose a fixed length or a variable length string?

First, the fixed-length disk space is more wasteful, but the efficiency is high. If the data basically determines the same length, it is fixed length.

Second, it saves disk space, but is inefficient. If the data does not determine the length, it can be used.

　　Text, literal string

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

Text strings are categorized according to the format of the stored data: Text OR blob.

Text: Storing text (binary data is actually stored as a path)

Blob: Stores binary data (usually not used).

　　Enum, enum string

　　All possible results are designed in advance, and the data stored must be one of the well-defined data.

How enumerations are used:

Definition: enum (List of elements that may appear); -such as enum (' Male ', ' female ', ' demon ', ' secrecy ');

Use: To store data, only the data defined above can be stored.

　　Function: 1, canonical data format, data can only be one of the specified data. The enumeration actually stores the value two, not the string itself.

2. Save storage space (enumeration usually has an alias: Radio box)

In MySQL, the system is automatically converted to data format, and basically the same as PHP.

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

　　Collection string

　　Collections and enumerations are similar: actual values are stored instead of strings (the collection is multi-selected).

Definition: Set (element list)

Use: You can use elements from the element list (multiple), separated by commas.

There is not an element in the collection.

The power of the collection is to be able to plan the data and save space, PHP can also standardize the data, but for PHP efficiency first, and data maintenance can be done by digital. PHP has no way of judging the data in the form of a database, the collection increases the maintenance of PHP.

　　

　MySQL record length

　MySQL specifies that no record can be longer than 65,535 bytes.

MySQL record: If any one of the fields is allowed to be empty, then the system automatically retains one byte from the entire record to store null.

　Column Properties

　　Column properties: The real constraint field is the data type, but the constraints on the data type are very simple and require some additional constraints to ensure the legitimacy of the data.

There are many column properties: Null/not NULL, Default,primary key,unique key,auto_increment,comment.

　　Empty properties: two values, null (NULL, default), and NOT NULL (no null)

　　Column Description: comment, description, no actual meaning, is specifically used to describe the field.

Default: Defaults to Guan Jian words.

MySQL basic note (vii)-data type two