6

For example if I had a code in PHP that updated the length, by alter table change column and setting the new length, to the longest entry there ever was would this affect greatly if I had 10000 records?

Does MySQL reallocate space in memory for each record of the new size even though the old data is shorter?

This question is MySQL specific

Steve
  • 3,673
  • 1
  • 19
  • 24
zardilior
  • 2,810
  • 25
  • 30
  • No, it does not. `varchar()` is a variable length string format. There is no need to change the formats -- except in cases where the length byte switches from 1 to 2 bytes. Note that if you had `char()`, then the data would actually change. – Gordon Linoff Dec 27 '14 at 21:23
  • Meaning??... I didn't quite understood the format is the way it is saved so unless you need two bytes it will always save it in one even if its smaller. But if the maximum length changed from 1 byte to two bytes what would happen to the old data would it be reallocated and therefore have a gross impact in a fat database? – zardilior Dec 27 '14 at 21:25
  • I think Golez Trol might be right thanks – zardilior Dec 27 '14 at 21:26
  • Though I didn't find any answer to be clear – zardilior Dec 27 '14 at 21:27
  • 1
    In MySQL 5.7, InnoDB can to an in-place `ALTER TABLE` as long as the size of the length field is the same. – Barmar Dec 27 '14 at 21:32

1 Answers1

12

The length of a varchar column is fixed to the length you declare when you create the table. The length can be any value from 0 to 255 (before MySQL 5.0.3) and from 0 to 65,535 (in MySQL 5.0.3 and later).

The storage of the varchar is the bytes of data with the addition of one or two bytes to declare the length of the string. If the maximum length is 255 or less then only 1 byte of length will be added.

If you use alter table and change the maximum length then no data storage size will be affected if the maximum length defined is below 255. If you're increasing the maximum length above 255 then it's up to the storage engine if it forces two bytes or not for values below 255, in that case it will increase by 1 byte for each row.

The char type is different to varchar as char always uses the space required, so if you had char(10) and varchar(10) but only stored "hello" in each, char would use all 10 bytes, vharchar would hold 6 bytes (5 for hello and 1 for the length), therefore changing the size of varchar columns won't allocate more storage space like it would if it was a char type.

The real question now is why would you want PHP to manipulate the varchar size? You should specify the size for a reason, if you want a variable length field that can hold a lot of text (more than 65,535 bytes) and also dynamic so it only uses the minimum space required maybe the TEXT types might be better for your situation?

Steve
  • 3,673
  • 1
  • 19
  • 24
  • Thanks a lot the thing is I'm designing an easy to use interface for non programmers to manipulate mysql directly and if you allow them to decide the varchar length they will do something they should not for example: must probably they will use 20 o 30 as the length to save emails and then be surprised they are getting cut after they try sending mails from this app or something of the sort. – zardilior Dec 27 '14 at 22:36
  • Thanks, I just changed the varchar datatype to char and it exceeds the size of the table. Know I understand why it happened. – heySushil Nov 22 '22 at 10:10