Wednesday 18 December 2019

mysql - find duplicate row in database if unique key (col1-col2-col3-col4-col5)build by using 5 column, fifth column with default null

[i have a table users, there is a unique key use 5 column for creating a unique key (user_name-email_id-dob-phone_no-address).]



CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_name` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_bin',
`email_id` VARCHAR(50) NOT NULL DEFAULT '0' COLLATE 'utf8_bin',
`dob` DATE NOT NULL,

`phone_no` BIGINT(20) NOT NULL DEFAULT '0',
`address` VARCHAR(25) NULL DEFAULT NULL COLLATE 'utf8_bin',
PRIMARY KEY (`id`),
UNIQUE INDEX `user_name` (`dob`, `address`, `phone_no`, `user_name`, `email_id`)
)

COLLATE='utf8_bin'
ENGINE=MyISAM
AUTO_INCREMENT=13
;



as earlier upload bulk data in table users by csv file using ignore duplicate option. and inserting data with null value in column address,



now i want to check the duplicate unique key data in my users table .



id|user_name|email_id|dob|phone_no|address|
1|roy|r@ro.co|42768|9992223|null|
2|roy|r@ro.co|42768|9992223|null|
3|roy|r@ro.co|42768|9992223|null|

4|roy|r@ro.co|42768|9992223|null|
5|roy|r@ro.co|42768|9992223|null|
6|roy|r@ro.co|42768|9992223|null|
7|me|m@me.co|42797|7897897899|null|
8|me|m@me.co|42798|7897897900|null|
9|me|m@me.co|42799|7897897901|null|
10|me|m@me.co|42800|7897897902|null|
11|st|s@st.co|42495|1122121212|null|
12|mr|m@mr.co|42129|141414|null|
13|mr|m@mr.co|42129|141414|null|

14|mr|m@mr.co|42129|141414|null|
15|mr|m@mr.co|42129|141414|null|
16|mr|m@mr.co|42129|141414|null|
17|mr|m@mr.co|42129|141414|null|
18|mr|m@mr.co|42129|141414|null|
19|mr|m@mr.co|42129|141414|null|
20|mr|m@mr.co|42129|141414|null|

No comments:

Post a Comment

php - file_get_contents shows unexpected output while reading a file

I want to output an inline jpg image as a base64 encoded string, however when I do this : $contents = file_get_contents($filename); print &q...