If I enter two Strings with only white space. I will Get this error message:
ERROR 1062: Duplicate entry ' ' for key 'PRIMARY'
How can I Turn off "Auto-Trim" ?
I'm Using this Charset: uft8-uft8_bin and This Datatype: Varchar.
Asked
Active
Viewed 3,596 times
7
Vijay
- 5,331
- 10
- 54
- 88
Simon Ludwig
- 113
- 3
- 9
-
1are you sure its a problem of auto trim? – Fathah Rehman P Feb 05 '13 at 08:51
-
Yes its a Primary Key and i'm not sure that it is a Problem of auto trim.. But how i can add Two empty String with different Lenght – Simon Ludwig Feb 05 '13 at 08:53
-
My first question will be why are entering a empty space into the column? Is that necessary? Also if its necessary are you sure that column should be a primary key? – Vijay Feb 05 '13 at 08:54
-
1please tell your requirement . i'll tell you the solution. Adding same strings with multiple space may not be a good idea – Fathah Rehman P Feb 05 '13 at 08:57
-
I'm converting an old Sybase Database to Mysql and it's necessary that the Sybase Datas equals the Mysql Datas. – Simon Ludwig Feb 05 '13 at 08:58
-
Follow up: http://stackoverflow.com/questions/34782594/mysql-space-equals-empty-string – Sebas Jan 14 '16 at 06:22
1 Answers
10
According to the SQL 92 documentation, when two strings are compared they are first made equal in length by padding the shortest string with spaces.
Search for 8.2 <comparison predicate> in the document.
If the length in characters of X is not equal to the length
in characters of Y, then the shorter string is effectively
replaced, for the purposes of comparison, with a copy of
itself that has been extended to the length of the longer
string by concatenation on the right of one or more pad char-
acters, where the pad character is chosen based on CS. If
CS has the NO PAD attribute, then the pad character is an
implementation-dependent character different from any char-
acter in the character set of X and Y that collates less
than any string under CS. Otherwise, the pad character is a
<space>.
So in other words. Its not about storing the value with the amount of spaces you entered, but its the comparisment it does to check for duplicate primary key. So you cannot have two strings with a different amount of spaces act as a primary key
Sebas
- 21,192
- 9
- 55
- 109
Hugo Delsing
- 13,803
- 5
- 45
- 72
-
2
-
3Onfortunately MYSQL doesnt allow you to change it. In the manual http://dev.mysql.com/doc/refman/5.5/en/char.html it says `All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces.` – Hugo Delsing Feb 05 '13 at 09:33