Shulou(Shulou.com)06/01 Report--

The size of the VARCHAR field can be modified by ALTER TABLE, command, and in-place, for example:

ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN C1 C1 VARCHAR (255)

This is true as long as the number of length bytes required by a VARCHAR column remains the same. This can be achieved as long as the number of bytes occupied by the field varchar is the same as the previous one after the field is modified. For example, for VARCHAR values between 0 and 255, only one bytes. The value of VARCHAR is 256 bytes or greater than 256 requires two bytes. In this way, only changes between 0 and 255are supported through in-place ALTER TABLE, or between 256 and greater than 256. in-place alter table does not support changing varchar values from less than 256 to values greater than 256. Because in this case, the stored bytes will change from 1 byte to two bytes. Can only be modified by algorithm=copy. For example, changing the value of varchar to 256in-place alter would will return an error.

ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN C1 C1 VARCHAR

ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change

Column type INPLACE. Try ALGORITHM=COPY.

The point that needs to be emphasized here is that it needs to be corresponding to different character sets. If it is English 0-255, if it is other character sets, then you need to pay attention to it because different character sets occupy different storage bits. Here we use Chinese to demonstrate.

CREATE TABLE `t1` (

`name` varchar (10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Insert into T1 values ('hu')

Commit

Mysql > ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN name name VARCHAR

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Mysql > ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN name name VARCHAR (86)

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Mysql >

Mysql > ALTER TABLE T1 ALGORITHM=INPLACE, CHANGE COLUMN name name VARCHAR (85)

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

If you change it to 100 and 86, it will be fine if you change it to 85. Doesn't the official document say that 0-255 is OK as long as the bits stored remain unchanged? Why not here?

In fact, the official document is correct! Because English is correct, but the storage space is different if it is changed to other character sets.

One Chinese character set placeholder UTF8 is three bit

85, 3, 255, so 85 can be changed to 86, which is more than 255, only through copy.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.