x

Best MySQL practice to insert a record if it does not already exist, and return its id

What is the best MySQL practice to insert a record if it does not already exist, and return its id? Imagine a simple 2-field table, with an auto-increment primary key (id) and a unique key VARCHAR field (name):

CREATE TABLE IF NOT EXISTS contact ( id bigint(13) NOT NULL auto_increment, name varchar(228) collate utf8_unicode_ci default NULL, PRIMARY KEY (id), UNIQUE KEY name (name) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

Most of the results of a search for the optimal solution to this requirement are based on a INSERT . . . ON DUPLICATE KEY UPDATE approach:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

  • I am quite confused as to why the general case is not a INSERT . . . ON DUPLICATE KEY SELECT (rather than UPDATE), especially when there is nothing to update. I simply want to return the id if it exists, and insert and return the id if it does not exist.
Note the unique key VARCHAR field is as long as it can be (228) without returning the error "Specified key was too long; max key length is 1000 bytes".
more ▼

asked Mar 25, 2012 at 04:59 AM in Default

stevenjgarner gravatar image

stevenjgarner
10 1 1 2

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest
Thanks Oleg. Yes, the logic you stated (again below) is what I was going for:

"if select (mysql_num_rows) >= 1 then select id else insert contact and mysql_insert_id()"

I was enquiring as to whether there is a preferred MySQL practice (e.g. using stored procedures or other approach) where a single query might suffice over a single MySQL connection, or whether the dual query approach (above) is always necessary to be implemented in another language - e.g. traditional approach using PHP:

<?php
if (isset($_POST['contact']) ? $_POST['contact'] : '')
{
$database = '';
$db_user = '';
$db_password = '';
$contact_safe = mysql_real_escape_string ($_POST['contact']);
$sql_select = "SELECT * FROM contact WHERE name LIKE '$contact_safe'";
$link = mysql_connect ('localhost', $db_user, $db_password ) or die(mysql_error());
mysql_select_db ($database, $link) or die(mysql_error());
$result_select = mysql_query ($sql_select) or die(mysql_error());
if ($result_select)
{
$count = mysql_num_rows ($result_select);
if ($count>=1)
{
$rows=mysql_fetch_array($result_select);
$id = $rows['id'];
}
else
{
$sql_insert = "INSERT INTO contact ( name) VALUES ('$contact_safe')";
$result_insert = mysql_query ($sql_insert) or die(mysql_error());
$id = mysql_insert_id();
}
}
else
{
echo "some error occurred";
}
mysql_close($link);
echo "The id for ".$contact_safe." is ".$id;
}

$html = "<form action = '' method = 'post'>
<input type = 'text' name = 'contact' />
<input type = 'submit' name = 'submit' value = 'Submit' />
</form>";

echo $html;
?>
more ▼

answered Apr 29, 2012 at 12:27 PM

stevenjgarner gravatar image

stevenjgarner
10 1 1 2

(comments are locked)
10|1200 characters needed characters left

Since the id column is auto-incremented and used as a PK of your table and name column is unique, you probably have to have a script which first checks the existence of the record with the specified name and if it does not then insert the record and return the value of the auto-incremented id, something like this:

if exists (select * from contact where name = @name) then
    select -1;
else
    insert into contact(name) values(@name);
    select last_insert_id();
end if;

Basically, you would want to always return something to the calling code, so if the record with the specified name already exists then you return -1. Otherwise, you insert a new record and select the value of the newly inserted id by calling the last_insert_id() function.

In any case, you don't want to pass the id as a parameter because the respective column is designed to auto-increment, so it will insert the correct value on auto-pilot. If you wish to return the value of id column for the record which already exists (rather than returning -1) then you can change the script and use something else in place of if exists. Something like this:

declare @id bigint(13);

set @id = (select id from contact where name = @name);

if (@id is null) then
    insert into contact(name) values(@name);
    set @id = (select last_insert_id());
end if;

select @id;

With this script, you will get back the id of already existing record (nothing inserted) or the id of newly inserted record after it has been inserted. Use this script if returning the id of already existing record is better then using - 1 for the same.

Oleg
more ▼

answered Mar 26, 2012 at 09:22 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x129
x108
x103
x68
x16

asked: Mar 25, 2012 at 04:59 AM

Seen: 13925 times

Last Updated: Apr 29, 2012 at 12:36 PM