CODEWITHSUNDEEP
javamysqlstringcharacter-encoding
0x56794E
0x56794E

Reputation: 21281

java string vs mysql string

mysql> CREATE database testing CHARACTER SET utf16;
Query OK, 1 row affected (0.00 sec)

mysql> USE testing;
Database changed

mysql> CREATE TABLE t (str varchar(64));
Query OK, 0 rows affected (0.43 sec)

mysql> INSET INTO t values ("1234567891234567");
Query OK, 1 row affected (0.00 sec)

=============================

Then I have a piece of java code

Connection connection = DriverManager.getConnection(url, usr, pass);
Statement statement = connection.createStatement();

statement.execute("USE  testing");

ResultSet rst = statement.executeQuery("SELECT str, LENGTH(str) FROM t;");
while (rst.next())
             System.out.print("java length: " + rst.getString(1).length() 
                     + "\nmysql length: " + rst.getInt(2));

This would give 

java length: 16
mysql length: 32

I dont understand why the lengths are different. I explicitly set the database's character set to UTF16 (which is the default charset of Java, right?) Why do I still get inconsistent length values?

Upvotes: 0

Views: 742

Answers (5)

Pau Kiat Wee
Pau Kiat Wee

Reputation: 9505

Mysql LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

Maybe what you need is CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

Upvotes: 3

paulsm4
paulsm4

Reputation: 121649

The mySql function "CHAR_LENGTH()" returns the #/Unicode characters (like Java's ".length").

The MySQL function "LENGTH()" returns the #/bytes.

Here's the MySQL reference:

Upvotes: 5

Andrew Cooper
Andrew Cooper

Reputation: 32576

My guess is that Java ios reporting the number of characters, while MySQL is reporting the number of bytes used to store the characters.

Upvotes: 0

Nurlan
Nurlan

Reputation: 673

try to connect in UTF-16 encoding:

Connection connection = DriverManager.getConnection(url+"?characterEncoding=UTF-16", usr, pass);

Upvotes: 0

mprabhat
mprabhat

Reputation: 20323

Default charset of Java is not UTF-16, its locale dependent.

If you want to change Charset in Java then you will have to do it before JVM startup, or else value of defaultCharset which is locale dependent will be used.

Upvotes: 0

Related Questions