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
  • 9,536
  • 6
  • 64
  • 82

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,206
  • 11
  • 83
  • 119
spencer7593
  • 103,596
  • 14
  • 107
  • 133
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
  • 141
  • 1
  • 6