Get duplicates values from values table

Create table
CREATE TABLE IF NOT EXISTS `emp1` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL
)

Insertion

INSERT INTO `emp1` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

SELECT `first_name`
FROM emp1
GROUP BY `first_name`
HAVING COUNT(*) = N

Change the HAVING condition to >=2
It outputs roy and joy are duplicates having more then one record wirh same value.

Change the HAVING condition to >1 to list non duplicate values, etc

INSERT IGNORE

Create table
CREATE TABLE IF NOT EXISTS `emp` (
`first_name` varchar(20) NOT NULL,
`last_name` varchar(20) NOT NULL,
PRIMARY KEY (`last_name`)
)

Insertion
INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’), (‘joy’, ‘josh’);

Useful Queries

INSERT IGNORE
If a record doesn’t have duplicate date then MySql inserts usual.
If a record have duplicate data, the IGNORE keyword instructs MySql to discard it with out generating any error message.

INSERT IGNORE INTO `emp` (`first_name`, `last_name`) VALUES (‘roy’, ‘raj’);

here insertion is failed but it won’t shows any error message.

INSERT INTO `emp` (`first_name`, `last_name`) VALUES (‘joy’, ‘raj’);

Here Mysql insertion is failed because here record is duplicate so MySql generate some error like.
#1062 – Duplicate entry ‘raj’ for key ‘PRIMARY

Sitepoint's twitaway experience for choosing right MySQL engine

Hi All,

Posting after a two month break sorry busy with new addition to our family my daughter(Manha). Here we go:

There is an interesting article about the experience Sitepoint developers had with twitaway application while distributing Free PDF who followed them. How they found the importance of choosing right MYSQL engine is how important :) please go through the full article for more details.

INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO

While reading and article came across one of powerful feature REPLACE INTO,I had used INSERT ON DUPLICATE KEY UPDATE but never used REPLACE INTO.

REPLACE works in a way that if the ID column(Primary Column) doesn't have the given value it will create a new entry in the table, however, IF the value exists already then it updates the existing entry in the table.

REPLACE INTO sessions VALUES ('$id', '$data', $ts).

Read about REPLACE INTO and Difference about INSERT ON DUPLICATE KEY UPDATE vs REPLACE INTO

MySQL PROCEDURE ANALYSE

It examines the result from a query and returns an analysis of the results that suggests optimal data types for each column. To obtain this analysis, append PROCEDURE ANALYSE to the end of a SELECT statement.

For Example:
SELECT col1, col2 FROM table1 PROCEDURE ANALYSE();
SELECT * FROM table1 PROCEDURE ANALYSE();

Quite common task during schema review is to find the optimal data type for the column value - for example column is defined as INT but is it really needed or may be SMALLINT or even TINYINT will do instead. Read the post in mysqlperformanceblog explained in more detail.

PROCEDURE ANALYSE() can be a double edged tool. In the first it will help you to find the optimal data type for column. In the second case it had no idea you needed bigger values and gave you a recommendation based on bad data. Like most tools, PROCEDURE ANALYSE() needs to be used properly to get the desired results.

Syntax and more info here.