varchar vs text - MySQL
Solution 1
To protect yourself from XSS attack, encode it using the htmlentities function.
Other than that, the choice of datatype has most to do with how big the content will be. If it may exceed 4048 characters, then use a text datatype. If many posts will be large, using a text datatype may reduce wasted data space and may perform slightly better than a giant varchar, but it depends upon your situation, you would be best to test the alternatives.
I generally prefer varchar because it's easier to deal with from a coding perspective, if nothing else, and fall back to text if the contents may exceed the size of a varchar.
Solution 2
Varchar is usually faster in retrieval when the size is reasonable, as it is stored within the table, where as TEXT is stored off the table with a pointer to location.
Thanks
Solution 3
(You have multiple questions; I will address the one that is in the title.)
The only difference between VARCHAR(4000)
and TEXT
is that an INSERT
will truncate to either 4000 characters or 65536 bytes, respectively.
For smaller values than 4000, there are cases where the temp table in a complex SELECT
will run faster with, for example, VARCHAR(255)
than TINYTEXT
. For that reason, I feel that one should never use TINYTEXT
.
Solution 4
it depends on the application behavior. space allocated inside block's table decrease space for other columns and decrease density data inside it. if full table scan is used by mysql, many blocks are scanned, it's inefficient. so it depends on your sql requests.
Related videos on Youtube
Sourav
Hi, I am Sourav Ghosh :) You can find my projects * AJAX Micro Mini Lib [JS] * Autorun Cleaner [VB.NET] * C Code Completer [VB.NET] * DockBar Develop [VB.NET] * ShoutOut-Twitter [ASP.NET + C#] * Ultra Light CAPTCHA [PHP] * Ultra Light Forum [PHP] * Wallpaper Changer [VB.NET]** @ http://sourceforge.net/users/sourav1989 I'll be really happy if you find them useful :) Thanks :)
Updated on June 04, 2022Comments
-
Sourav almost 2 years
in my project an user can write comment [plain text], and view others comment, can delete own comment, but can not update comment !
In this case which would should i use ?Text or Varchar(4048) ?
What is the advantage and disadvantage of Text and Varchar(large like 4000) ?
Is it secure enough if i replace only '<' with '& lt;' and '>' with '& gt;' to make sure everything is fine ?
[i dont want to convert all those like ' " & ..., to save space, i just want to make sure user can not write javascript]There will be a limit on the front end
-
Sourav about 13 yearswill it be faster too if the varchar size is 4000 ? And thnx for replying :)
-
Just a PHP Programmer about 13 yearsIt depends on what hardware you are using, you would better do a benchmark for it. Thanks...
-
Sourav about 13 yearsi was just waiting to hear that XSS, how can someone do XSS with out < and >, coz i think without < and > the script lost it's power, it will be appreciated if you can give some example of exactly which character to convert(htmlentitles) to be safe ?
-
squawknull about 13 yearsThere is some potential for increased data file fragmentation in many databases if you have a lot of large values stored in varchars. With mysql, it gets even more complex as it depends upon how the specific storage engine works (and this is always subject to change). Hence, if I think most of the fields will be on the smaller end of the scale, just use varchar. If many will be large, use text.
-
squawknull about 13 yearsDon't try to piecemeal the characters. Particularly as you get outside of the UTF8 characterset, things get very tricky. Using the htmlentities function is the simplest, best bet you have, and I can't think of a reason not to just use it versus trying to do something else yourself. There are lots of examples at php.net/manual/en/function.htmlentities.php.
-
zac1987 over 12 yearsusing TEXT won't increase data file fragmentation?
-
Rick James over 7 yearsThe "stored off the table" is referring to certain
ROW_FORMATs
in InnoDB. BothVARCHAR(4000)
andTEXT
are subject to such, so this is not really a difference. -
Rick James over 7 years
htmlentities
should be used when displaying text, not when storing into the database.