Menu
I am converting one of the "Varchar" column in mysql table to a text field. I am simply using single alter command to convert the type. When i read about text and varchar came to know that it had difference in storing mechanisms.
Am I supposed to write any migration script to change the type of column and move data or a single type altering command is enough ?
878
There is no a big problem to change varchar to text because text supports more data length than varchar , but if the field has a index it must be drop and create new index with prefix col_name(length) (see CREATE INDEX syntax).
More Details. TEXT has a fixed max size of 2¹⁶-1 = 65535 characters. VARCHAR has a variable max size M up to M = 2¹⁶-1 . So you cannot choose the size of TEXT but you can for a VARCHAR .
In most circumstances, VARCHAR provides better performance, it's more flexible, and can be fully indexed. If you need to store longer strings, use MEDIUMTEXT or LONGTEXT, but be aware that very large amounts of data can be stored in columns of these types.
Some Differences Between VARCHAR and TEXT A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index. VARCHAR is stored inline with the table (at least for the MyISAM storage engine), making it potentially faster when the size is reasonable.
ALTER TABLE table_name MODIFY column_name TEXT NOT NULL; There is no a big problem to change varchar to text because text supports more data length than varchar, but if the field has a index it must be drop and create new index with prefix col_name(length) (see CREATE INDEX syntax).
According to your data content maybe would be a good idea use fulltext indexes but that implies change your search expressions on that field.
If you are in production environment, the table will be locked meanwhile the migration is in progress to prevent data loss.
38
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
