I want to change column datatype from text to varchar(256) and the table has many data is there a chance of data loss or error i can encounter ? or a downtime?

CodePudding user response：

Assuming you are using Innodb tables on MySQL 8, changing the type of a column needs to rewrite the table.

alter table will wait until all ongoing writes are done (insert, update, delete) and then read lock the table while it does the copy. Only selects will be allowed. Inserts, updates, and deletes will all wait until the copy is finished.

ALTER TABLE operations that use the COPY algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the ALTER TABLE operation executes, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table.

Once the copy is done, it will briefly lock the table to complete the copy.

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

You will lose data if any of your text values are longer than 255 characters; the values will be truncated.

There isn't much practical difference between text and varchar so the value of such a change on a large table is questionable.