کار با رکوردهای/سطرهای تکراری در MySQL
لینک کوتاه چهارشنبه , 13 مرداد 1395 7 دیدگاه
سلام
قطعا پیش آمده براتون که در دیتابیس رکوردهای تکراری براساس یک فیلد رخ بده
مثلا ، کاربری با نام کاربری یکسان منتها بصورت حروف بزرگ و کوچیک ثبت نام کرده باشه Reza , reza rEzA
یا حتی مثلا شخصی به اسم رضا اقدام به ارسال چندین نظر کرده باشه
یا اینکه یکی از مطالب اخبارتون دو بار ثبت شده باشه
علی ای حال ، قطعا این مساله رخ میدهد
و اما ما میخوایم ببینیم با اینها چکار کنیم
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `users` (`id`, `username`, `name`) VALUES
(1, 'reza', 'رضا'),
(2, 'ReZa', 'رضا'),
(3, 'Ali', 'علی'),
(4, 'ali2', 'علی2'),
(5, 'ali', 'علی'),
(6, 'ALi', 'علی با حرف بزرگ'),
(7, 'ali', 'علی با حرف کوچیک');
خب ابتدا یه خروجی بگیریم ببینم نتیجه چی هستSELECT * FROM `users` ;
خب نتیجه 7 رکورد هست که رکورد 5 با هفت کاملا برابر هستن خب میخوایم این تکراری نشون داده نشه
SELECT * FROM `users` group by username;
خب اوضاع درست شد ظاهرا اما میبینیم رکورد 1 با 2 و رکوردهای 5و3و6 فقط از نظر بزرگی کوچکی تفاوت دارن خب بیاییم اینم درست کنیم
SELECT * FROM `users` group by LOWER(username);
خب الان تقریبا خروجیمون درسته ، بیاییم یه کار دیگه هم بکنیم ببینیم از هر رکورد تکراری چند تا وجود داره
SELECT LOWER(username),count(LOWER(username)) FROM `users` group by LOWER(username);
خب آنچه مشخص هست رکوردهایی که تعدادشون بیشتر از یک هست تکراری هستن پس فقط نیاز به اونها داریم
SELECT LOWER(username),count(LOWER(username)) FROM `users` group by LOWER(username) having count(LOWER(username))>1 ;
خب تا اینجا تقریبا یه سری اطلاعات از رکوردهای تکراری گرفتیم ، اما اصل ماجرا اینجاست میخوایم این رکورهای تکراری رو
از دیتابیس حذف کنیم یعنی مثلا کاربری که با یوزر ali ثبت نام کرده چندین بار فقط یکی از رکورهاش بمونه و بقیه حذف بشه
قدم اول اینه ، لیست کاربران با یوزرنیم یکتا رو در میاریم به همراه کلید اصلی یا همون آی دی
SELECT id,username,count(username) FROM `users` group by lower(username) ;
در این نتیجه باید id رو جدا کنیم
select id from (SELECT id,username,count(username) FROM `users` group by lower(username)) _d
سپس با یک سابکوئری اعلام میکنیم که در جدول users کلیه رکوردها پاک شود به غیر از این شناسه id ها
delete from users where id not in (select id from (SELECT id,username,count(username) FROM `users` group by lower(username)) _d )
و حالا مشاهده جدول
select * from users
یه کار دیگه هم بکنیم ، نام کاربری ها رو کلا به حروف کوچیک تبدیل کنیم
update users set username=LOWER(username)
و حالا مشاهده جدول مجددا
select * from users
امیدوارم از این آموزش لذت برده باشید
با تشکر ، رضا شیخله
نظرات :
» محسن
@ 02 شهریور 1395 [8 سال قبل]
سلام
چطوری میشه ارتباط تو پایگاه داده رو از طریق URL محدود کنیم؟