asked
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".
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
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;
?>
"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;
?>
3 People are following this question.
