This is the paradigm of
ALTER TABLE. It is a simpler approach to making changes. Keep in mind that MySQL supports an open storage engine plugin architecture. The same calls to perform every operation fits InnoDB, MyISAM, and other storage engines.
Under the hood, doing
ALTER TABLE tblname MODIFY COLUMN mycolumn VARCHAR(255);
would mechanically work like this
CREATE TABLE tblname_new LIKE tblname;
ALTER TABLE tblname_new MODIFY COLUMN mycolumn VARCHAR(255);
INSERT INTO tblname_new SELECT * FROM tblname;
DROP TABLE tblname;
ALTER TABLE tblname_new RENAME tblname;
This approach is totally storage engine agnostic.
The alternative to this would be for the storage engine to actively manage a list of blocks that can hold new rows.
What happens when you deal with...
Even if such management was in place, it would never get summoned because none of the fragments would be large enough to hold changed rows since the column had a smaller size before. This would result in appending all the rows to the physical file for the table. Now, the space occupied by the rows before the change would appear before the first of the table after the
ALTER TABLE was done. This would be true for expanding CHAR variables.
In the case of expanding a VARCHAR, altering lengths to be larger may require changing from 1 to 2 bytes for every row should you expand a VARCHAR(255) (255 is a 1-byte unsigned number) to VARCHAR(256) (256 is a 2-byte unsigned number). The introduction of that one extra byte would still require vigorous fragmentation block list management just for the sake of accommodating one extra byte. Therefore, actual space management via maintaining a fragmentation block list would be highly impractical for the open storage engine plugin architecture of MySQL. Such management would probably be in Oracle, SQL Server and PostgreSQL.