List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 3 2005 8:08pm
Subject:Re: varchar and trailing spaces...
View as plain text  
At 15:39 -0400 9/3/05, Jason Pyeron wrote:
>I have found out that some files on the system, have trailing spaces...
>To make matters worse I have found 'file ' and 'file  ' both exist, 
>breaking constraint "name".
>So am I better off going to TINYTEXT or VARBINARY(255) for the name field?

VARBINARY also is subject to trailing space stripping.

Note: If you use MySQL 5.0.3 or up, VARCHAR/VARBINARY do *not* strip
trailing spaces.  Earlier than that,  you probably should use TINYTEXT.

>That said another way, will TINTTEXT break the index (and index speed)?
>Will VARBINARY break the way the Connector/J handles the strings 
>(they are utf8)?
>   id int(11) NOT NULL auto_increment,
>   typeref tinyint(4) NOT NULL default '1',
>   name varchar(255) NOT NULL default '',
>   ppathref int(11) default NULL,
>   PRIMARY KEY  (id),
>   UNIQUE KEY name (name,ppathref),
>   KEY ppathref (ppathref),
>   KEY typeref (typeref),
>   CONSTRAINT paths_ibfk_1 FOREIGN KEY (ppathref) REFERENCES paths (id),
>   CONSTRAINT paths_ibfk_2 FOREIGN KEY (typeref) REFERENCES pathtypes (id)

Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
varchar and trailing spaces...Jason Pyeron3 Sep
  • Re: varchar and trailing spaces...Paul DuBois3 Sep