Re: switching from varchar to tinytext

[Jason Pyeron]

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     -
- [phone]                 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.