0

Here is my table 

`id` int(11) NOT NULL,
`notifyroles` varchar(50) NULL,
 PRIMARY KEY  (`id`)
 ENGINE=MyISAM  DEFAULT CHARSET=utf8

I use it to store a single set of dynamic values of an array that is imploded to string such as item1,item2,item3 and when I pull the data from the db I will explode those values again.

When I initialize my software I insert row id 1 and then leave the notifyroles element as NULL until I used it.

It will and should never have any other rows but row 1 and so I chose not to use the auto increment feature. I never use INSERT I always just use UPDATE for id 1.

Since i dont want to have to write a bunch of code to check for more rows and truncate it and reset it if there is and all of that stuff my question is:

Is there a way to lock the table so that it cannot have more than 1 row? And if someone tried to INSERT another row it would fail.

ps. I am hoping that with the evolution of MySQL that maybe after all this time there is such a way.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129

2 Answers2

1

Simplest is to manage the rights so the user your software uses has no insert rights but does have update rights on that table.

See: http://dev.mysql.com/doc/refman/5.7/en/grant.html

  • Thanks yeah i found some examples online as well. The main thing is the localhost user, it would have to be generic. So what i was hoping i could do is to modify the table for privy's as i wanted in phpMyAdmin and then export it and see how the query was done and then just dup it. But i am unable to find that option under this tables option or field option. –  May 22 '16 at 00:48
  • You can add a user for your application. And give that user whatever rights you like. –  May 22 '16 at 00:52
  • Oh OK now i think i got it, just use the username for the db itself and then specify the table field privy... makes sense ;) –  May 22 '16 at 00:55
0

There's not really a way to lock a table, but you can take advantage of MySQL triggers. As the name suggest, they are activated immediately at the time the specified action is performed, in this case, an insert. Maybe try this:

CREATE TRIGGER locktable
AFTER INSERT ON mytable
FOR EACH ROW
BEGIN
  IF (NEW.id != --id of the row you want protected--) THEN
    DELETE FROM mytable WHERE id = NEW.id;
  END IF;
END;

Why not BEFORE INSERT? some strategies suggest causing the query to fail, but I'm really not comfortable with that approach.

I hope it helps.

Kirito
  • 307
  • 2
  • 7
  • It can be done with `BEFORE INSERT` trigger if you will throw some error (for example, with [`SIGNAL`](https://dev.mysql.com/doc/refman/5.5/en/signal.html)) instead of deleting row. – Andrew May 22 '16 at 06:28
  • Really great idea, now thats thinking outside the box Kirito, Nice :) –  May 22 '16 at 08:37