Ticket 1031: Replace UPDATE and INSERT queries by REPLACE INTO - FluxBB core

Download

Tickets

Milestones

Subscribe 2

Ticket #1031 (open enhancement)

Replace UPDATE and INSERT queries by REPLACE INTO

Created: 2015-04-11 11:35:14
Reported by: chris98
Assigned to: None

Milestone: None
Component: code
Priority: normal

In admin_forums.php there is this code:

// Run an UPDATE and see if it affected a row, if not, INSERT

$db->query('UPDATE '.$db->prefix.'forum_perms SET read_forum='.$read_forum_new.', post_replies='.$post_replies_new.', post_topics='.$post_topics_new.' WHERE group_id='.$cur_group['g_id'].' AND forum_id='.$forum_id) or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

if (!$db->affected_rows())
	$db->query('INSERT INTO '.$db->prefix.'forum_perms (group_id, forum_id, read_forum, post_replies, post_topics) VALUES('.$cur_group['g_id'].', '.$forum_id.', '.$read_forum_new.', '.$post_replies_new.', '.$post_topics_new.')') or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

Wouldn't it make more sense to just use REPLACE INTO?

// Run an UPDATE and see if it affected a row, if not, INSERT

$db->query('REPLACE INTO '.$db->prefix.'forum_perms (group_id, forum_id, read_forum, post_replies, post_topics) VALUES('.$cur_group['g_id'].', '.$forum_id.', '.$read_forum_new.', '.$post_replies_new.', '.$post_topics_new.')') or error('Unable to insert group forum permissions', __FILE__, __LINE__, $db->error());

History

chris98 2015-04-11 11:36:43

Description changed. (Diff)

adaur 2015-04-11 11:57:15

According to http://www.quora.com/In-MySQL-when-shou … KEY-UPDATE

If you utilize REPLACE INTO and you have an AUTO_INCREMENT field (i.e. index/id column), that auto increment column will be incremented.

We don't want that.

http://stackoverflow.com/questions/2110 … -vs-update

Replace was designed to ease the following case:
Check, if record with same PK exists
If yes, update the record with the given one
If no, insert a new record
As per documentation REPLACE is equivalent to INSERT, apart from deleting any existing record having the PK is beeing deleted before.

REPLACE INTO will delete the existing row and insert a new one. I'm not sure that we want this behaviour.

Development