varchar and text field type understanding in MySQL

Recently a few students asked me varchar and text have what do not, this problem, previously said really did not much collation, before the text in the design is as far as possible to the other table, to keep the main table as small as possible, so that the InnoDB BP cache more data.

Today, the system of opportunity to clean up, mainly from the storage, the maximum value, the default value of several aspects of comparison.

BTW: from the ISO sql:2003 varchar is a standard type, but text is not (including tinytext). VarChar only supports 5.0.3 before MySQL 0-255byte, and 5.0.3 only after 0-65535byte.

From storage:

-Text is to be entered into the overflow store. Also for the text field, it is not present with the row data. But in principle not all overflow,
There will be 768 bytes and the original rows stored in a piece, more than 768 of the rows exist and row the same page or other page.

-varchar within MySQL belongs to a structure developed from a blob, innobase in earlier versions, and overfolw storage after 768 bytes.

-For Innodb-plugin: Overflow storage for variable-length field processing after 20Byte
(Under the new Row_format: Dynimic compress)
After talking about storage, say the disadvantages of using these large variable-length fields:

-In Innobase, the variable length field is stored as much as possible into a page, so that if you use these large variable-length fields, you will be able to accommodate rows in a page
Few, in the query, although not query these large fields, but also will be loaded into the INNODB buffer pool, equal to the wasted memory.
(Buffer pool is cached by page) (not one page will add random io)

-In Innodb-plugin, in order to reduce the waste of memory in this large variable-length field, the introduction of more than 20 bytes, both for overflow storage,
and want to not save to the same page, in order to add a page can store more rows, improve the buffer pool utilization. It also requires us,
Do not read those fields that are longer if you do not need them specifically.

Is that a problem? Why is the varchar (255+) store similar to text, but why do you have varchar, mediumtext, and text types?
(varchar, which is greater than 255 from storage, can be said to be converted to text.) That's why varchar is more than 65535 and will turn into Mediumtext.

I understand: This is one aspect of compatibility, on the other hand varchar and text are different on non-null defaults. On the whole the function is still different.

This also involves the extra cost of the field:

The code is as follows	Copy Code
-varchar less than 255byte 1byte overhead -varchar greater than 255byte 2byte overhead -Tinytext 0-255 1 byte overhead -Text 0-65535 byte 2 byte overhead -Mediumtext 0-16m 3 byte overhead -Longtext 0-4GB 4byte Overhead

Note overhead refers to how many bytes are required to record the actual length of the field.

From the processing form to say varchar is greater than 768 bytes, essentially storage and text differences are not too big. Basically considered to be the same.
Also from the 8000byte point to illustrate: for Varcahr, text if the line does not exceed 8000byte (about the number of InnoDB data page half), overflow will not be saved to another page. Based on the above features can be summed up as text is just a MySQL extension out of the special syntax has a sense of compatibility.

Default value problem:

-For the text field, MySQL does not allow default values.
-VARCHAR allows default values

Summarize:

Based on the storage implementation: you can consider using varchar instead of Tinytext
If a non-empty default value is required, you must use the varchar
If the stored data is greater than 64K, you must use the Mediumtext, Longtext
varchar (255+) and text are the same in the storage mechanism

Special attention needs to be paid to the fact that varchar (255) is not only 255byte, but is likely to occupy more.

Special attention, varchar large characters will reduce performance, so in the design is still a principle large segment to be torn out, the main table is still as small as possible
Type of source code:

The code is as follows	Copy Code
+--FIELD_STR (abstract) | +--field_longstr |  | +--field_string |  | +--field_varstring |  | +--field_blob |     | +--field_geom | | | +--field_null | +--field_enum | +--field_set

Test SQL and methods

The code is as follows

Copy Code

CREATE TABLE tb_01 ( C1 varchar (255), C2 varchar (255), C3 varchar (255), C4 varchar (255), C5 varchar (255), C6 varchar (255), C7 varchar (255), C8 varchar (255), C9 varchar (255), C10 varchar (255), C11 varchar (255) ) Engine=innodb; Insert into tb_01 (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11) VALUES (Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu, 255 '), repeat (' Miss '), 255 ), repeat (' Wu ', 255)); ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using row_format=dynamic or row_format=compressed. In the current row format, the BLOB prefix of 768 bytes is stored inline. (testing) Root@localhost [wubx]> Set global Innodb_file_format=barracuda; Query OK, 0 rows Affected (0.00 sec) (testing) Root@localhost [wubx]> ALTER TABLE tb_01 row_format=dynamic; Query OK, 0 rows affected (0.19 sec) records:0 duplicates:0 warnings:0 (testing) Root@localhost [wubx]> Insert into tb_01 (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11) VALUES (Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu, 255 '), repeat (' Miss '), 255 ), repeat (' Wu ', 255)); Query OK, 1 row Affected (0.00 sec) Set global innodb_file_format=antelope; CREATE TABLE tb_02 ( C1 varchar (2000), C2 varchar (2000), C3 varchar (2000), C4 varchar (2000), C5 varchar (2000), C6 varchar (2000), C7 varchar (2000), C8 varchar (2000) ) Engine=innodb; INSERT into tb_02 (c1, c2, C3,C4,C5,C6,C7,C8) VALUES (Repeat (' Wu ', Watts), repeat (' Wu ', Watts), repeat (' Wu ', Watts), repeat (' Wu ', ), repeat (' Wu ', Watts), repeat (' Wu ', Watts), repeat (' Wu ', Watts), repeat (' Wu ', 2000));

The code is as follows

Copy Code

CREATE TABLE tb_03 ( C1 text, C2 text, C3 text, C4 text, C5 text, C6 text, C7 text, C8 text, C9 text, C10 text, C11 text ) Engine=innodb; Insert into tb_03 (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11) VALUES (Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu, 255 '), repeat (' Miss '), 255 ), repeat (' Wu ', 255)); (testing) Root@localhost [wubx]> Insert into tb_03 (C1,C2,C3,C4,C5,C6,C7,C8,C9,C10,C11) VALUES (Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), Repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu ', 255), repeat (' Wu, 255 '), repeat (' Miss '), 255 ), repeat (' Wu ', 255)); ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using row_format=dynamic or row_format=compressed. In the current row format, the BLOB prefix of 768 bytes is stored inline. Set global Innodb_file_format=barracuda; ALTER TABLE tb_03 row_format=dynamic;