String type in column type in MySQL tutorial

This article mainly introduces you to the relevant knowledge of the string type in mysql. I hope it will be helpful to friends in need!

Recommended reference tutorial: "mysql tutorial"

Column type (data type)type in column type in MySQL tutorial>

The so-called column type actually refers to data Type, that is, a unified classification of data, from a system perspective is to be able to manage it in a unified way and make better use of limited space.

In SQL, data types are divided into three major categories: Numeric type, string type and date and time type.

String typetype in column type in MySQL tutorial>

In SQL, the string type is divided into String type in column type in MySQL tutorial categories, namely: char , varchar, text, blob, enum and set.

Category String type in column type in MySQL tutorial: Fixed-length string

Fixed-length string: char, that is, the disk (two-dimensional table) has already determined the final data storage when defining the structure. length.

• char(L): L represents Length, that is, the length that can be stored, in characters, the maximum length is String type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorial;

• char(String type in column type in MySQL tutorial): Indicates that in UTFString type in column type in MySQL tutorial environment, String type in column type in MySQL tutorial*String type in column type in MySQL tutorial=String type in column type in MySQL tutorialString type in column type in MySQL tutorial bytes are required.

Category String type in column type in MySQL tutorial: Variable-length string

Variable-length string: varchar, that is, allocating storage space When the space is allocated, it is allocated according to the maximum space, but the actual amount used is determined based on specific data.

• varchar(L): L represents Length, the theoretical length is String type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorial, but there will be String type in column type in MySQL tutorial to String type in column type in MySQL tutorial more bytes to determine the actual length of storage;

• varchar(String type in column type in MySQL tutorial0): For example, to store String type in column type in MySQL tutorial0 Chinese characters, in UTFString type in column type in MySQL tutorial environment, String type in column type in MySQL tutorial0*String type in column type in MySQL tutorial String type in column type in MySQL tutorial=String type in column type in MySQL tutorialString type in column type in MySQL tutorial bytes are required.

In fact, if the storage length exceeds String type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorial characters, neither a fixed-length string nor a variable-length string is used, but a text stringtext .

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

• Fixed-length strings are a waste of disk space, but are highly efficient: if the data is basically the same length, use fixed-length strings, such as ID cards, phone numbers, etc.;

• Variable-length strings save disk space, but are inefficient: If the length of the data cannot be determined (different data changes), use variable-length strings, such as addresses and names. wait.

Category String type in column type in MySQL tutorial: Text string

If the amount of data is very large, usually more than String type in column type in MySQL tutorialString type in column type in MySQL tutorialString type in column type in MySQL tutorial characters, a text string will be used.

Text strings are classified according to the storage format and can be divided into:

• text: store text;

• blob: stores binary data (which are actually storage paths), usually not used.

Category String type in column type in MySQL tutorial: Enumeration string

Enumeration string:enum, you need to combine all possibilities in advance The results that appear are all designed, and the actual data stored must be one of the specified data.

How to use enumeration strings:

• Definition: enum('Element String type in column type in MySQL tutorial', 'Element String type in column type in MySQL tutorial', 'Element String type in column type in MySQL tutorial'... ), for example enum('male','female','confidential');

• ##Usage: The stored data can only be defined in advance Good data.

Execute the following SQL statement to create an enumeration table for testing:

-- 创建枚举表create table my_enum(
    gender enum(&#String type in column type in MySQL tutorial9;男&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;女&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;保密&#String type in column type in MySQL tutorial9;)
)charset utfString type in column type in MySQL tutorial;

Execute the following SQL statement again to add the table to the table## Insert test data into #my_enum

: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">-- 插入测试数据insert into my_enum values (&amp;#String type in column type in MySQL tutorial9;男&amp;#String type in column type in MySQL tutorial9;),(&amp;#String type in column type in MySQL tutorial9;女&amp;#String type in column type in MySQL tutorial9;),(&amp;#String type in column type in MySQL tutorial9;保密&amp;#String type in column type in MySQL tutorial9;);insert into my_enum values (&amp;#String type in column type in MySQL tutorial9;male&amp;#String type in column type in MySQL tutorial9;);</pre><div class="contentsignin">Copy after login</div></div>

Through the above test, we can find that there is an advantage to using enumeration strings, that is:

Standard Data format, the data inserted into the table can only be certain data that has been defined in advance.

In addition, enumeration strings also have another function, that is:

Save storage space (enumeration data usually has an alias). Enumerations actually store numerical values ​​instead of The string itself.

In MySQL, the system has the function of automatically converting data formats. Here, we can prove that the enumeration field stores numerical values. The specific method is: take out the data

0

. If it is a string, the final result will always be 0, otherwise it will be other values. . <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">-- 验证枚举字段实际存储的格式select gender + 0,gender from my_enum;</pre><div class="contentsignin">Copy after login</div></div>

Observing the above results, we can find out the actual rules for enumerating elements, that is, numbering starts from

String type in column type in MySQL tutorial

according to the order in which the elements appear. Next, let’s understand the principle of enumeration:

• 枚举在进行数据规范（定义）的时候，系统会自动建立一个数字与枚举元素的对应关系（放在日志中）；在进行数据插入的时候，系统自动将字符串转换为对应的数值进行存储；在进行数据提取的时候，系统自动将数值转换成对应的字符串进行显示。

通过阅读以上枚举的原理，咱们可以知道：使用枚举的效率并不高（低于其他类型的数据），但能规范数据和节省存储空间。

第 String type in column type in MySQL tutorial 类：集合字符串

集合字符串：set，跟枚举类似，实际存储的是数值而不是字符串。

集合字符串的使用方式：

• 定义：set，元素列表；

• 使用：可以使用元素列表中的多个元素，用逗号分隔。

执行如下 SQL 语句创建枚举表，进行测试：

-- 创建集合表create table my_set(
    hobby set(&#String type in column type in MySQL tutorial9;音乐&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;电影&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;旅行&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;美食&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;摄影&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;运动&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;宠物&#String type in column type in MySQL tutorial9;)
)charset utfString type in column type in MySQL tutorial;

再执行如下 SQL 语句，向表my_set中插入测试数据：

-- 插入测试数据insert into my_set values (&#String type in column type in MySQL tutorial9;电影,美食,宠物&#String type in column type in MySQL tutorial9;);insert into my_set values (String type in column type in MySQL tutorial);

再执行如下 SQL 语句，查看表my_set中的数据：

-- 查看数据select hobby + 0,hobby from my_set;

观察上面的结果，相信大部分童鞋也懵啦！对于String type in column type in MySQL tutorial还好理解，String type in column type in MySQL tutorial=String type in column type in MySQL tutorial+String type in column type in MySQL tutorial，对应于集合中数据的编号，也正是音乐和电影；但是String type in column type in MySQL tutorialString type in column type in MySQL tutorial是什么鬼啊？在此，咱们不妨将集合（&#String type in column type in MySQL tutorial9;音乐&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;电影&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;旅行&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;美食&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;摄影&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;运动&#String type in column type in MySQL tutorial9;,&#String type in column type in MySQL tutorial9;宠物&#String type in column type in MySQL tutorial9;）中的元素选中的记为String type in column type in MySQL tutorial，没有选中的记为0，表示成二进制，也就是：

• 0String type in column type in MySQL tutorial0String type in column type in MySQL tutorial00String type in column type in MySQL tutorial

再将上面的二进制反过来：

• String type in column type in MySQL tutorial00String type in column type in MySQL tutorial0String type in column type in MySQL tutorial0

不妨算算，上述二进制对应的十进制数，即为String type in column type in MySQL tutorialString type in column type in MySQL tutorial.

到这里，相信大家已经恍然大悟啦，原来：集合字符串中每一个元素都对应一个二进制位，其中被选中的为String type in column type in MySQL tutorial，未选中的为0，最后在反过来，这个二进制数对应的十进制数即为其数据库中实际存储的是数值。

此外，集合字符串中插入元素的顺序并没有影响，最终系统都会自动去匹配集合的顺序，即：

-- 插入测试数据insert into my_set values (&#String type in column type in MySQL tutorial9;电影,美食,旅行&#String type in column type in MySQL tutorial9;);insert into my_set values (&#String type in column type in MySQL tutorial9;旅行,电影,美食&#String type in column type in MySQL tutorial9;);

上述两个 SQL 语句会产生相同的结果：

如上图所示，显然咱们的结论得到了验证。

最后，集合的原理同枚举类似，因此可以的到相同的结论，即：使用集合的效率并不高（低于其他类型的数据），但能规范数据和节省存储空间。

The above is the detailed content of String type in column type in MySQL tutorial. For more information, please follow other related articles on the PHP Chinese website!