MySQL Character set

The official document indicates that a row has a maximum of 65,535 bytes. Utf-8 character Test 1 build a Chinese character 3 bytes in Utf-8

By calculating 65535/3, you can draw a maximum of 21845 per row, build a table

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e6%9c%80%e5%a4%a7%e5%ad%97%e7%ac%a6%e5%a4%b1%e8%b4%a5.jpg?version=1&modificationdate=1432004499000& Api=v2 "alt="%e6%9c%80%e5%a4%a7%e5%ad%97%e7%ac%a6%e5% "/>

The reason for creation failure is that the first two characters of varchar (N) are the length of the table is varchar. When the character is empty, the storage is from the second bit starting from the 2nd bit, i.e. n= (65535-2-1)/3 =21845

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e6%9c%80%e5%a4%a7%e5%ad%97%e7%ac%a6-1.jpg?version=1&modificationdate=1432004695000&api=v2 "alt="%E6% 9C%80%E5%A4%A7%E5%AD%97%E7%AC%A6-1.J "/>

2 Insert data comparison table byte change

View empty table occupies a null table of bytes 16384

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e7%a9%ba%e8%a1%a8.jpg?version=1&modificationdate=1432004876000&api=v2 "alt="%E7%A9%BA%E8%A1%A8.jpg? Version=1&modific "/>

Inserting data

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e6%8f%92%e5%85%a5.jpg?version=2&modificationdate=1432022834000&api=v2 "alt="%E6%8F%92%E5%85%A5.jpg? Version=2&modific "/>

View the table changes and calculate (note this is the test to insert two lines after the 2 to be removed)

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e8%8c%b6%e5%90%8e.jpg?version=1&modificationdate=1432005228000&api=v2 "alt="%E8%8C%B6%E5%90%8E.jpg? Version=1&modific "/>

Calculates the size of the table 147456/2-16384-1=65535 (1 is the byte occupied by the callout 1 row in the table)

Number of Characters View Select Char_length (A1), length (A1) from Tb_utf8;

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e5%ad%97%e7%ac%a6%e6%95%b0.jpg?version=1&modificationdate=1432005676000&api=v2 "alt="%E5%AD%97%E7%AC% A6%e6%95%b0.jpg?version= "/>

Verify that the validation is empty and not empty when the character occupies

varchar (21844) takes up 65,533 bytes tinyint type occupies one of its own 65535=65532+2+1

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e4%b8%8d%e4%b8%ba%e7%a9%ba.jpg?version=1&modificationdate=1432014634000&api=v2 "alt="%E4%B8%8D%E4%B8% Ba%e7%a9%ba.jpg?version= "/>

VARCHAR is null-time validated

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e7%a9%ba.jpg?version=1&modificationdate=1432014857000&api=v2 "alt="%e7%a9%ba.jpg?version=1& Modificationdate "/>

Can be seen as empty need a byte to mark, table cannot create

Conclusion: VarChar occupies 3 bytes in a Chinese character in Utf-8, and the numbers and letters occupy one byte. A byte is needed to mark the empty space.

GBK Character Set test build table: encoding a Chinese character according to GBK takes 2 bytes, a row has a maximum of 65,535 bytes, and the top test is empty when it is more than one byte. You can calculate the value of varchar (n) n in the null and non-empty values of varchar

Null (65535-2-1)/2 = 32,766 Chinese characters non-empty (65535-2)/2=32766.5 is also a non-empty state can build more than one tinyint type

VARCHAR null-built table

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /k.jpg?version=1&modificationdate=1432021239000&api=v2 "alt=" k.jpg?version=1&modificationdate= 1432021 "/>

varchar non-empty build table

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /fk.jpg?version=1&modificationdate=1432021270000&api=v2 "alt=" fk.jpg?version=1&modificationdate= 143202 "/>

Note: The character type is converted by default if varchar exceeds the specified number of bytes when building a table

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /c.jpg?version=1&modificationdate=1432021367000&api=v2 "alt=" c.jpg?version=1&modificationdate= 1432021 "/>

View the amount of data inserted after inserting data

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /%e6%8f%92%e5%85%a5.jpg?version=2&modificationdate=1432022834000&api=v2 "alt="%E6%8F%92%E5%85%A5.jpg? Version=2&modific "/>

View the bytes consumed by the data

Insert Front Table

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /q.jpg?version=1&modificationdate=1432022919000&api=v2 "alt=" q.jpg?version=1&modificationdate= 1432022 "/>

After inserting

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /h.jpg?version=1&modificationdate=1432022941000&api=v2 "alt=" h.jpg?version=1&modificationdate= 1432022 "/>

conclusion: In GBK character, varchar (N), n<=23766 characters. If n is greater than 32766 MySQL will default to the conversion character type. The GDK can save 32,766 characters or digital letters.

Latin1 Character Set test

1character=1byte, 1 kanji =2character,

Build table because a byte = 1 characters in Latin1, when the table is built, varchar (n) n is empty when varchar is not empty n=65532 n=65533

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /1.jpg?version=1&modificationdate=1432027180000&api=v2 "alt=" 1.jpg?version=1&modificationdate= 1432027 "/>

Insert letters and Kanji separately to view insert data results

650) this.width=650; "class=" Confluence-embedded-image "src=" http://wiki.op.xywy.com/download/attachments/5736618 /j1.jpg?version=1&modificationdate=1432028402000&api=v2 "alt=" j1.jpg?version=1&modificationdate= 143202 "/>

You can see the inserted characters and Chinese characters by calculating the insertion of kanji 65523, inserting kanji (65523-1)/2 kanji.

Show the reason for incorrect length of Chinese characters

When inserting a kanji is if the kanji is greater than the value of varchar (n) (N-1)/2, n is an odd number. The inserted value is: Number of Chinese characters +1 empty character number of Chinese characters = (N-1)/2

Conclusion:latin1 (n) n can insert up to 65,523 letters or numbers to insert N/2 characters

MySQL Character set