{"id":26687,"date":"2016-09-14T09:14:14","date_gmt":"2016-09-14T07:14:14","guid":{"rendered":"https:\/\/mamchenkov.net\/wordpress\/?p=26687"},"modified":"2016-09-14T09:14:14","modified_gmt":"2016-09-14T07:14:14","slug":"mysql-php-and-integrity-constraint-violation-1062-duplicate-entry","status":"publish","type":"post","link":"https:\/\/mamchenkov.net\/wordpress\/2016\/09\/14\/mysql-php-and-integrity-constraint-violation-1062-duplicate-entry\/","title":{"rendered":"MySQL, PHP and “Integrity constraint violation: 1062 Duplicate entry”"},"content":{"rendered":"\n

Anna Filina<\/a> blogs about an interesting problem<\/a> she encountered with when working on a PHP and MySQL project:<\/p>\n

MySQL was complaining about \u201cIntegrity constraint violation: 1062 Duplicate entry\u201d. I had all the necessary safeguards in my code to prevent duplicates in tha column.<\/p>\n

I gave up on logic and simply dumped the contents of the problematic column for every record. I found that there was a record with and without an accent on one of the characters. PHP saw each as a unique value, but MySQL did not make a distinction, which is why it complained about a duplicate value. It\u2019s a good thing too, because based on my goal, these should have been treated as duplicates.<\/p><\/blockquote>\n

She also mentions two possible solutions to the problem:<\/p>\n

My solution was to substitute accented characters before filtering duplicates in the code. This way, similar records were rejected before they were sent to the database.<\/p><\/blockquote>\n

and<\/p>\n

As pointed out in the comments, a more robust and versatile solution would be to check the collation on the column.<\/p><\/blockquote>\n

I’m sure this will come in handy one day.<\/p>\n\n","protected":false},"excerpt":{"rendered":"\n

Anna Filina blogs about an interesting problem she encountered with when working on a PHP and MySQL project: MySQL was complaining about \u201cIntegrity constraint violation: 1062 Duplicate entry\u201d. I had all the necessary safeguards in my code to prevent duplicates in tha column. I gave up on logic and simply dumped the contents of the … Continue reading MySQL, PHP and “Integrity constraint violation: 1062 Duplicate entry”<\/span><\/a><\/p>\n\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"MySQL, PHP and \"Integrity constraint violation: 1062 Duplicate entry\" #PHP #MySQL #unicode #databases #WebDev","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false,"_links_to":"","_links_to_target":""},"categories":[1,18,62,1334],"tags":[1559,3066,38,23,1330],"keyring_services":[],"class_list":["post-26687","post","type-post","status-publish","format-standard","hentry","category-general","category-programming","category-technology","category-web-work","tag-databases","tag-mysql","tag-php","tag-unicode","tag-web-development"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t