Insert Delayed with MySQL

Delayed Inserts

You can delay INSERT's from happening until the table is free by using the DELAYED hint in your SQL statement. For example:

INSERT DELAYED INTO table (col) VALUES ('val');

The above SQL statement will return quickly, and it stores the insert statement in a memory queue until the table your inserting into is free from reads. This means that if there are multiple inserts in the queue they can be written in one block, which is a more optimal use of IO.

The downside to this is that it is not transactionally safe at all. You don't really know how long its going to take for your INSERT to happen. If the server crashes, or is forcefully shutdown you will loose your INSERTs. So don't use this on any critical information that would suck to loose.

One great use for the DELAYED keyword would be for storing web stats in a database. You don't want the client waiting for the stats to insert, and its not that big of a deal if you loose a few stats (for most people).

Status Variables

The following status variables can be used to monitor your delayed inserts:

Delayed_errors
Delayed_insert_threads
Delayed_writes
Not_flushed_delayed_rows

You can get the values by running the SQL statement: SHOW STATUS

How to use MySQL STUFF

The STUFF function is not a MySQL function and is specific to SQL Server. However, MySQL has a native function that works in the same way as the STUFF function. In this article, we will see how to use the MySQL INSERT function as an equivalent, what its parameters are for and some practical examples.

MySQL INSERT Function

The MySQL INSERT function adds a piece of text to the main string according to the parameters that we indicate.

How does it work? What parameters should I use to call it? We will find the answer to those questions below.

Syntax to execute INSERT function

A regular call to the INSERT function receives four parameters, namely the main string, start position, number, and new text.

1. INSERT(main-string, start-position, number, new-text)

INSERT or STUFF SQL examples

Example 1 – How to insert a substring at the end.

Imagine that we have the following string “We are going to use the INSERT function on this sentence.“. In this example, we want to replace all characters after the phrase “INSERT function” with an ellipsis “…”. We note that there are 39 characters that we want to keep (from the start of the sentence, up to the phrase “INSERT function“).

Parameters of example 1:

Parameters to execute example 1

Command to run

1. SELECT INSERT("We are going to use the INSERT function on this sentence.", 40, 18, "...");

Result:

We are going to use the INSERT function...

Example 2 – How to insert a substring at the beginning

Imagine we have the following string as the main-string: “INSERT function“. In this example, we are going to insert a substring (“STUFF function vs “) at the start of the main-string.

Parameters of example 2:

Parameters to execute example 2

Command to run

1. SELECT INSERT("INSERT function", 1, 0, "STUFF function vs");

Result:

STUFF function vs INSERT function

Example 3 – How to insert a substring in the middle of the main-string

Finally, let’s see how to insert a substring in the middle of “We are going to use the INSERT function on this sentence.”. Let’s try inserting the substring “MySQL ” in the middle of our main-string, right before the phrase “INSERT function“.

Parameters of example 3:

Parameters to execute example 3

Command to run

1. SELECT INSERT("We are going to use the INSERT function on this sentence.", 25, 0, "MySQL ");

Result:

We are going to use the MySQL INSERT function on this sentence.

Important considerations to keep in mind

1. The start-position parameter can go from 1 to the length of main-string.
2. If the start-position parameter is equal to 0, then INSERT function returns main-string.
3. If start-position > main-string length, then INSERT function returns main-string.

How to Insert a String into another String in MySQL using INSERT()

In MySQL, you can use the INSERT() function to insert a string into another string.

You can either replace parts of the string with another string (e.g. replace a word), or you can insert it while maintaining the original string (e.g. add a word). The function accepts 4 arguments which determine what the original string is, the position with which to insert the new string, the number of characters to delete from the original string, and the new string to insert.

Here’s the syntax:

INSERT(str,pos,len,newstr)

Where str is the original string, pos is the position that the new string will be inserted, len is the number of characters to delete from the original string, and newstr is the new string to insert.

Replace a Word

Here’s an example where I use INSERT() to replace a word within a string:

SELECT INSERT('Cats and dogs', 6, 3, 'like');

Result:

Cats like dogs

This effectively replaces the word and with the word like. I used 6 because the word and started at the 6 character mark, and I used 3 because that’s how many characters I want to delete (the word and is 3 characters long).

Insert a Word

Here I simply insert a word without deleting anything from the original string:

SELECT INSERT('Cats and dogs', 10, 0, 'big ');

Result:

Cats and big dogs

The reason this doesn’t delete anything from the original string is because I specified 0 (which means zero characters should be deleted).

Out of Range Values

If you specify a position that’s outside the length of the original string, MySQL will return the original string unchanged.

Example:

SELECT INSERT('Cats and dogs', 20, 4, 'rabbits');

Result:

Cats and dogs

Here’s another example where I use a negative starting position:

SELECT INSERT('Cats and dogs', -1, 4, 'rabbits');

Result:

Cats and dogs

This is one of the differences between MySQL’s INSERT() function and Transact-SQL‘s STUFF() function. In T-SQL, the STUFF() function would return NULL in these cases.

Inserting NULL Values

Another area where MySQL’s INSERT() differs to T-SQL’s STUFF() is with NULL values. If you try to insert a NULL value, MySQL will return NULL.

SELECT INSERT('Cats and dogs', 6, 3, NULL);

Result:

NULL

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row.

For example,

insert into table (id, name, age) values(1, "A", 19)

Let’s say the unique key is id, and in my database there is a row with id = 1. In that case I want to update that row with these values. Normally this gives an error. If I use insert IGNORE it will ignore the error, but it still won’t update.

 Answers

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

---

Try this out:

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Hope this helps.

---

When using batch insert use the following syntax:

INSERT INTO TABLE (id, name, age) VALUES (1, "A", 19), (2, "B", 17), (3, "C", 22)
ON DUPLICATE KEY UPDATE
    name = VALUES (name),
    ...

---

When using SQLite:

REPLACE into table (id, name, age) values(1, "A", 19)

Provided that id is the primary key. Or else it just inserts another row. 

---

INSERT INTO table (id, name, age) VALUES (1, 'A', 19) ON DUPLICATE KEY UPDATE id = id + 1;

Also do not forget to concern about the unique key constraint.

ALTER TABLE `table` ADD UNIQUE `unique_key` ( `id` ) 

Mysql: “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”

While executing an INSERT statement with many rows, I want to skip duplicate entries that would otherwise cause failure. After some research, my options appear to be the use of either:

• ON DUPLICATE KEY UPDATE which implies an unnecessary update at some cost, or
• INSERT IGNORE which implies an invitation for other kinds of failure to slip in unannounced.

Am I right in these assumptions? What's the best way to simply skip the rows that might cause duplicates and just continue on to the other rows?

 Answers

I would recommend using INSERT...ON DUPLICATE KEY UPDATE.

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

• Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
• Inserting a NULL into a column with a NOT NULL constraint.
• Inserting a row to a partitioned table, but the values you insert don't map to a partition.

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

• A new auto-increment ID is allocated.
• Dependent rows with foreign keys may be deleted (if you use cascading foreign keys) or else prevent the REPLACE.
• Triggers that fire on DELETE are executed unnecessarily.
• Side effects are propagated to replication slaves too.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

---

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+

---

Something important to add: When using INSERT IGNORE and you do have key violations, MySQL does NOT raise a warning!

If you try for instance to insert 100 records at a time, with one faulty one, you would get in interactive mode:

Query OK, 99 rows affected (0.04 sec)

Records: 100 Duplicates: 1 Warnings: 0

As you see: No Warnings! This behavior is even wrongly described in the official Mysql Documentation.

If your script needs to be informed, if some records have not been added (due to key violations) you have to call mysql_info() and parse it for the "Duplicates" value.

---

I routinely use INSERT IGNORE, and it sounds like exactly the kind of behavior you're looking for as well. As long as you know that rows which would cause index conflicts will not be inserted and you plan your program accordingly, it shouldn't cause any trouble.

---

Replace Into seems like an option. Or you can check with

IF NOT EXISTS(QUERY) Then INSERT

This will insert or delete then insert. I tend to go for a IF NOT EXISTS check first.

---

If using insert ignore having a SHOW WARNINGS; statement at the end of your query set will show a table with all the warnings, including which IDs were the duplicates.

MySQL: Insert record if not exists in table

I am trying to execute the following query:

INSERT INTO table_listnames (name, address, tele)
VALUES ('Rupert', 'Somewhere', '022')
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name='value'
);

But this returns an error. Basically I don't want to insert a record if the 'name' field of the record already exists in another record - how to check if the new name is unique?

 Answers

I'm not actually suggesting that you do this, as the UNIQUE index as suggested by Piskvor and others is a far better way to do it, but you can actually do what you were attempting:

CREATE TABLE `table_listnames` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `tele` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Insert a record:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

Try to insert the same record again:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'Rupert'
) LIMIT 1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
+----+--------+-----------+------+

Insert a different record:

INSERT INTO table_listnames (name, address, tele)
SELECT * FROM (SELECT 'John', 'Doe', '022') AS tmp
WHERE NOT EXISTS (
    SELECT name FROM table_listnames WHERE name = 'John'
) LIMIT 1;

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

SELECT * FROM `table_listnames`;

+----+--------+-----------+------+
| id | name   | address   | tele |
+----+--------+-----------+------+
|  1 | Rupert | Somewhere | 022  |
|  2 | John   | Doe       | 022  |
+----+--------+-----------+------+

And so on...

---

Worked : 

INSERT INTO users (full_name, login, password) 
  SELECT 'Mahbub Tito','tito',SHA1('12345') FROM DUAL
WHERE NOT EXISTS 
  (SELECT login FROM users WHERE login='tito');

---

INSERT IGNORE INTO `mytable`
SET `field0` = '2',
`field1` = 12345,
`field2` = 12678;

Here the mysql query, that insert records if not exist and will ignore existing similar records.

----Untested----

---

To overcome similar problem, I have made the table I am inserting to have a unique column. Using your example, on creation I would have something like:

name VARCHAR(20),
UNIQUE (name)

and then use the following query when inserting into it:

INSERT IGNORE INTO train
set table_listnames='Rupert'

---

This query works well:

INSERT INTO `user` ( `username` , `password` )
    SELECT * FROM (SELECT 'ersks', md5( 'Nepal' )) AS tmp
    WHERE NOT EXISTS (SELECT `username` FROM `user` WHERE `username` = 'ersks' 
    AND `password` = md5( 'Nepal' )) LIMIT 1

And you can create the table using following query:

CREATE TABLE IF NOT EXISTS `user` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(30) NOT NULL,
    `password` varchar(32) NOT NULL,
    `status` tinyint(1) DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Note: Create table using second query before trying to use first query.

---

insert into customer_keyskill(customerID, keySkillID)
select  2,1 from dual
where not exists ( 
    select  customerID  from customer_keyskill 
    where customerID = 2 
    and keySkillID = 1 )

---

I had a problem, and the method Mike advised worked partly, I had an error Dublicate Column name = '0', and changed the syntax of your query as this`

     $tQ = "INSERT  INTO names (name_id, surname_id, sum, sum2, sum3,sum4,sum5) 
                SELECT '$name', '$surname', '$sum', '$sum2', '$sum3','$sum4','$sum5' 
FROM DUAL
                WHERE NOT EXISTS (
                SELECT sum FROM names WHERE name_id = '$name' 
AND surname_id = '$surname') LIMIT 1;";

The problem was with column names. sum3 was equal to sum4 and mysql throwed dublicate column names, and I wrote the code in this syntax and it worked perfectly,

---

This query can be used in PHP code.

I have an ID column in this table, so I need check for duplication for all columns except this ID column:

#need to change values
SET @goodsType = 1, @sybType=5, @deviceId = asdf12345SDFasdf2345;    


INSERT INTO `devices` (`goodsTypeId`, `goodsId`, `deviceId`) #need to change tablename and columnsnames
SELECT * FROM (SELECT @goodsType, @sybType, @deviceId) AS tmp
WHERE NOT EXISTS (
    SELECT 'goodsTypeId' FROM `devices` #need to change tablename and columns names
    WHERE `goodsTypeId` = @goodsType
        AND `goodsId` = @sybType
        AND `deviceId` = @deviceId
) LIMIT 1;

and now new item will be added only in case of there is not exist row with values configured in SET string