How to cut a String to fit a TEXT field in MySQL

Question

I'm writing a String into a database which has a field of type TEXT. This means I can write 65,535 bytes into that field. After that I will get a SQL Exception:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'DETAILS' at row 1

So my question is now, how can I check my String and then also how can I cut the String at the correct position that it still fits into the database.

I know it depends also on the encoding. I just saw that the MySQL schema uses the "latin1 - default collection" charset.

See here: http://stackoverflow.com/questions/3186343/automatically-trimming-length-of-string-submitted-to-mysql — Perception, Apr 22 '13 at 15:16
Short answer: String.getBytes(). Look at http://stackoverflow.com/a/4385653/1919228 — Pablo Lozano, Apr 22 '13 at 15:18
Ok - this gives me the size of the String in latin1 (ISO-8859-1). But how do I split the String now at this position? — Philipp, Apr 22 '13 at 15:19
MySQL STRICT mode cannot be changed... I need to do this in Java — Philipp, Apr 22 '13 at 15:19

score 1 · Accepted Answer · answered Apr 22 '13 at 15:28

I think I got it with this code:

public String cutStringForTEXT(String string) {
    String ENCODING = "ISO-8859-1";
    int FIELD_SIZE = 65535;
    try {
        byte[] bytes = string.getBytes(ENCODING);
        if (bytes.length >= FIELD_SIZE) {
            return new String(Arrays.copyOfRange(bytes, 0, FIELD_SIZE), ENCODING);
        }
    } catch (UnsupportedEncodingException ex) {
    }
    return string;
}

If it's too long, then I get the subset of the array of bytes and convert it back into a String.

score 0 · Answer 2 · answered Apr 22 '13 at 15:20

0

Of you are using php to insert the value in your database, you can let php check the size of the variable before you insert it into the database. If the variable is too long, you can let it chop off the part that exceeds the size limit.

answered Apr 22 '13 at 15:20

Xandervr

74
5

How to cut a String to fit a TEXT field in MySQL

2 Answers2