SQLSTATE [23000]：完整性约束违规：密钥qr_codes_encoded_key_unique的重复条目“B”

Question

I have already visited following question.

#1062 - Duplicate entry '' for key 'unique_id' When Trying to add UNIQUE KEY (MySQL)

I have made an string column 'encoded_key' as unique. I have inserted 'b' in that column at first and then I want to add 'B' in the column. I think 'b' and 'B' are different and unique but it shows following error:

Illuminate\Database\QueryException SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'B' for key 'qr_codes_encoded_key_unique'

Are cases ignored in unique constraint in mysql ?

I am using mysql in laravel application.

Any kind of suggestions are appreciated.

Answer 1

u need to change or set custom collation to utf8_bin

 $table->string('encoded_key')->unique()->collation('utf8_bin');

you may also specify character with collation

for example : set utf8 also like this

$table->string('encoded_key')->unique()->charset('utf8')->collation('utf8_bin');

for more information read this article