1

I have a table items with id as primary key, and two more fields activatedby and active.

CREATE TABLE `items`. ( `id` BIGINT NOT NULL AUTO_INCREMENT , `active` BOOLEAN NOT NULL DEFAULT FALSE , `activatedby` BIGINT NOT NULL DEFAULT '0' , PRIMARY KEY (`id`)) ENGINE = InnoDB;

The script gets an array with 100 entries with an id-number and 1 or 0 for active or not active. [$id,1] or [$id,0] . activatedby should always be set to the same $userid.

Being a beginner I a natural query would look to me like

UPDATE items (id, active, activatedby)
VALUES
('622057', 1, '17389551'),
('2913659', 0, '17389551'),

and so on .....

('6160230', 1, '17389551')

My problem, I just restarte writing in php a bit after years, and because getting suggested to use prepared statements and binding parameters, how this binding would work on such an UPDATE statement? (If my not tested statement is valid at all.)

But even more I worry about speed, as in the same thread and in the php documentation I read preparing statements could cause much overhead, and it might be MUCH faster to use multi_query and mysqli_real_escape_string instead.

I don't like the binding syntax at all, and would anyway prefere to use mysqli_real_escape_string, but even less I would like to use an old method that probably will be deprecated sometimes.

Just to boil my question down: 1. multi_query or the syntax above? 2. prepared statements and binding or using mysqli_real_escape_string?

Bill2022
  • 29
  • 9
  • Did you even try this "natural" query? WITHOUT "prepared statements and binding parameters", just as is? – Your Common Sense Feb 25 '22 at 18:22
  • Use `REPLACE INTO`. See https://dev.mysql.com/doc/refman/8.0/en/replace.html – Barmar Feb 25 '22 at 18:24
  • 2
    I can't find any reference to any [UPDATE ... VALUES](https://dev.mysql.com/doc/refman/8.0/en/update.html) feature and I can't wrap my head around its possible meaning. Do you want to `INSERT` rows? Whatever, 100 rows is a tiny amount of data and perhaps it's too early to worry about performance when you don't even have working code. – Álvaro González Feb 25 '22 at 18:32
  • @Barmar - `REPLACE INTO` is almost never the best solution, especially with the advent of IODKU. `REPLACE` does both a `DELETE` and an `INSERT`. And, unless you specify the auto-incr value, it changes that. IODKU is _either_ an `INSERT` or `UPDATE`. – Rick James Feb 26 '22 at 18:35
  • IODKU can be batched into a single Update-like query for maximal speed. (But 100 separate updates, even with binding, is a trivial amount of work.) – Rick James Feb 26 '22 at 18:37

0 Answers0