• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What happens when the length of the text exceed the limit of the column VARCHAR

 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use VARCHAR(4000) for a column in a table in MySQL. 4000 is the max char allowed in MySQL. What will happen if the text length exceeds the 4000? Does the DB (MySQL or Oracle) automatically chop off anything that above 4000, but still allows you to insert or update the text of the first 4000 chars? Or the DB will reject the entire message if it over 4000? Any error messages?

How the length of the text is measured for the non-English languages, the raw length of the encoding protocol (e.g., UTF8)?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34870
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most databases give you an error. I recommend trying it to be sure on your database.
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know how about MySQL, but in Oracle the 4000 size limit counts in bytes. Therefore, if your String in database encoding is represented using some multi-byte characters, it may not fit into a VARCHAR2 though its length in Java is less than 4000 characters.

I don't know how to determine the size of the String in database encoding in a general case. You'd probably have to obtain the database encoding and use it to convert the String into a byte array, then look at the length of the array. It might be easier to just catch the database exception that would be generated by using a String that is too long.

If you do need to store longer strings, you'll need to use a CLOB (maximal size of a CLOB is database dependent, but generally counts in the gigabytes).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic