1
mysql> CREATE TABLE primary2(boom text,id int,PRIMARY KEY(boom(5)) );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO primary2 VALUES('viok',1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO primary2 VALUES('viok',1);
ERROR 1062 (23000): Duplicate entry 'viok' for key 'PRIMARY'
mysql> INSERT INTO primary2 VALUES('viok ',1);
ERROR 1062 (23000): Duplicate entry 'viok ' for key 'PRIMARY'

STRINGs 'viok' and 'viok ' are different .How to add a string with spaces?

zloctb
  • 10,592
  • 8
  • 70
  • 89

2 Answers2

3

Q: How to add a string with spaces?

A: It's not possible.

The TEXT values 'viok' and 'viok ' are not unique, in terms of how MySQL stores those as index entries. This is documented behavior:

If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors will occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. This is not true for BLOB columns.

Kermit
  • 33,827
  • 13
  • 85
  • 121
spencer7593
  • 106,611
  • 15
  • 112
  • 140
0

MySQL ignores trailing spaces in string comparisons. One way is to add a column to store number of trailing spaces the text has.

One way is to store it as varchar, which allows trailing spaces.

ssbh
  • 151
  • 1
  • 7