CODEWITHSUNDEEP
mysqlbulkinsert
prince
prince

Reputation: 123

Insert multiple values together in MY-SQL which not exist. where "NULL" should be unique

I have table structure. 

CREATE TABLE test_duplicate_join 
(    
vendor INT,
buyside INT,
columnnme VARCHAR(32),
mappingid INT ,
columntype VARCHAR(32) 
);
ALTER TABLE test_duplicate_join ADD PRIMARY KEY(mappingId);
ALTER TABLE test_duplicate_join
MODIFY mappingId INT NOT NULL AUTO_INCREMENT;
CREATE UNIQUE INDEX idx_name_phone ON test_duplicate_join(vendor,buyside,columnnme,columntype);

INSERT INTO test_duplicate_join (vendor,buyside,columntype) 
VALUES  (0,34,'Excel'),(0,35,'A');

After this, if I try to insert (0,34, 'Excel'),(1,36, 'fd') together, then it should insert only 2nd value.

I tried with insert ignore(MYSQL feature) where I am making unique id for all columns.

But there is an issue if values have null, then unique doesn't work. My data can have null values. But that also should be unique.

I know this is expected behavior. But how can I get my required functionality?

If two rows have same data apart my auto increment id, Then it's duplicate.

Update 1:: For single insert, we can do like this if "columnnme" is only can be null. For others columns, we can put the case. How to do for multiple inserts together? 

INSERT INTO test_duplicate_join (vendor,buyside,columnnme) SELECT 0,0,NULL 
WHERE NOT EXISTS 
    (   SELECT  1 FROM test_duplicate_join d   WHERE
    d.vendor = 0
    AND
    d.buyside = 0
    AND
    d.columnnme = (CASE WHEN ISNULL(NULL)  THEN columnnme IS NULL ELSE
                    columnnme=NULL END) )

Upvotes: 0

Views: 154

Answers (2)

foo
foo

Reputation: 68

This appears to be expected MySQL behaviour.

See Bug #8173: unique index allows duplicates with null values (https://bugs.mysql.com/bug.php?id=8173) which has been marked as Not a bug.

Upvotes: 0

P.Salmon
P.Salmon

Reputation: 17655

Just to clarify the problem

MariaDB [sandbox]> CREATE TABLE test_duplicate_join
    -> (
    -> vendor INT,
    -> buyside INT,
    -> columnnme VARCHAR(32),
    -> mappingid INT ,
    -> columntype VARCHAR(32)
    -> );
Query OK, 0 rows affected (0.20 sec)

MariaDB [sandbox]> ALTER TABLE test_duplicate_join ADD PRIMARY KEY(mappingId);
Query OK, 0 rows affected (0.37 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]> ALTER TABLE test_duplicate_join
    -> MODIFY mappingId INT NOT NULL AUTO_INCREMENT;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]> CREATE UNIQUE INDEX idx_name_phone ON test_duplicate_join(vendor,buyside,columnnme,columntype);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO test_duplicate_join (vendor,buyside,columntype)
    -> VALUES  (0,34,'Excel'),(0,35,'A');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> INSERT INTO test_duplicate_join (vendor,buyside,columntype)
    -> VALUES  (0,34,'Excel'),(0,36,'fd');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [sandbox]>
MariaDB [sandbox]> select * from test_duplicate_join;
+--------+---------+-----------+-----------+------------+
| vendor | buyside | columnnme | mappingId | columntype |
+--------+---------+-----------+-----------+------------+
|      0 |      34 | NULL      |         1 | Excel      |
|      0 |      34 | NULL      |         3 | Excel      |
|      0 |      35 | NULL      |         2 | A          |
|      0 |      36 | NULL      |         4 | fd         |
+--------+---------+-----------+-----------+------------+
4 rows in set (0.00 sec)

Upvotes: 1

Related Questions