To test the char vs byte index I created a unique bcol(2), on a tiny text
utf8 field.
I then preceded to insert the numbers 00..99 in Chinese whose hex values
are below. Since we are using utf8 vs ucs2 they are going to be 3 bytes
per char, totaling 6 bytes per numbers.
If the index is indeed 2 bytes then there will by a constraint failure,
and an error generated.
This did not happen on the inserts of 00..99, I added another test of 100,
I expected it to fail base on it being a duplicate of '10'0, it did.
Since my intended application was using varchar(255) and a tinytext's max
length is 255, I am not concerned with my data exceeding the index width
without error.
I leave this open for review and comment
results from test script, in-lined below
mysql> select count(distinct left(hex(bcol),4)) as _4,
count(distinct left(hex(bcol),6)) as _6,
count(distinct hex(bcol)) as full from testingtable;
+----+----+------+
| _4 | _6 | full |
+----+----+------+
| 6 | 10 | 100 |
+----+----+------+
1 row in set (0.02 sec)
import java.sql.*;
public class test
{
public static void main(String[] args) throws Throwable
{
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection C=DriverManager.getConnection(args[0]);
C.createStatement().executeUpdate("DROP TABLE if exists testingtable");
C.createStatement().executeUpdate("create table testingtable( id int not null
primary key auto_increment, vcol varchar(2) not null, bcol tinytext not null,
unique(bcol(2)) );");
PreparedStatement stmt=C.prepareStatement("INSERT INTO testingtable (vcol, bcol)
VALUES (?,?)");
String[] cnumbers=
{
"\u96F6", //zero
"\u4e00", //one
"\u4e8c", //two
"\u4e09", //three
"\u56DB", //four
"\u4E94", //five
"\u516D", //six
"\u4E03", //seven
"\u516B", //eight
"\u4E5D", //nine
"\u5341" //ten
};
for (int i=0; i<10; ++i)
{
for (int ii=0; ii<10; ++ii)
{
String val=cnumbers[i]+cnumbers[ii];
stmt.setString(1,val);
stmt.setString(2,val);
stmt.executeUpdate();
}
}
System.err.println("Now fail on 100");
String val=cnumbers[1]+cnumbers[0]+cnumbers[0];
stmt.setString(1,val);
stmt.setString(2,val);
stmt.executeUpdate();
}
}
On Mon, 5 Sep 2005, Jason Pyeron wrote:
On Sun, 4 Sep 2005, Alexey Polyakov wrote:
Why do you choose to convert varchar to tinytext? Doesn't look like
good idea to me.
trailing spaces and mysql 4.x
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
- -
- Jason Pyeron PD Inc. http://www.pdinc.us -
- Partner & Sr. Manager 7 West 24th Street #100 -
- +1 (443) 921-0381 Baltimore, Maryland 21218 -
- -
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
This message is for the designated recipient only and may contain
privileged, proprietary, or otherwise private information. If you
have received it in error, purge the message from your system and
notify the sender immediately. Any other use of the email by you
is prohibited.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
