I was hoping that the documentation was wrong. Here is the specific example in the documentation that says that varchar is padded:
If a given value is stored into the CHAR(4) and VARCHAR(4) columns, the values retrieved from the columns are not always the same because trailing spaces are removed from CHAR columns upon retrieval. The following example illustrates this difference:
mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO vc VALUES ('ab ', 'ab ');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT CONCAT('(', v, ')'), CONCAT('(', c, ')') FROM vc;
+---------------------+---
| CONCAT('(', v, ')') | CONCAT('(', c, ')') |
+---------------------+---
| (ab ) | (ab) |
+---------------------+---
1 row in set (0.06 sec)
--------------------------
Thanks for your answer. I messed up on the max store for TEXT. I had written TEXT off because I had seen posts about it being 3 times slower than varchar.
Thank you very much. I'll be running storage and speed tests today!
Lynn
Main Topics
Browse All Topics
by: BitsqueezerPosted on 2009-11-07 at 06:24:17ID: 25766416
Hi Lynn,
maybe you should take a look at this page of the documentation:
http://dev.mysql.com/doc/r
Even in the page you quoted there is a line saying "VARCHAR values are not padded when they are stored."
The sense of varchar (for which it is prefixed with "var" as variable) is to store and give back only the number of bytes you want up to the maximum length. The same with the "TEXT" type with the difference that it can be very much longer and it has disadvantages with performance. Only the type "CHAR" is padded with spaces because this is a type of fixed-length which gives better performance but need more bytes to save. CHAR is normally used for very short strings like indicators , i.e. "A","B","C" or something like that.
VARCHAR can be used in your case if your sourcecode not exceeds 64 KB which is the maximum possible length of VARCHAR but also the maximum possible length of the complete record so normally it must be a little bit shorter depending on the other fields of the record.
To save sourcecode it is the best to use an extra table which only has a column for an ID and a column with TEXT type where you can save your sourcecode. Next is a link to the table you want to use where you only save the ID of the TEXT table. This gives better performance as your normal table don't need to save the sourcecode, only give it back when you need it. For example in an endless form you would display a short term describing the contents of the sourcecode but not the sourcecode itself which would be displayed if the user clicks this line and opens a detail form - then a sourcecode field in the form can display the sourcecode getting it from the TEXT table. This gives you maximum performance for the normal table.
Moreover you can use the same TEXT table for any other purpose in your database where you need a TEXT column with the same method as you only must link to the ID of this table.
Cheers,
Christian