Problems identified by executing insert into on duplicate key update statements in php. - Codes Helper - Programming Question Answer

Problems identified by executing insert into on duplicate key update statements in php.

in the php project, you simply connect to mysql. through the medoo library Due to the need for batch update configuration operations, insert into. Was used On duplicate key update statement, which cannot pass the syntax check of pdo. The php code is as follows:

$medoo = new Medoo($dbcfg);

$sql = "INSERT INTO `t_config` (`name`,`value`) VALUES ("WEB_SITE_TITLE","11dd11"),("WEB_SITE_KEYWORD","22rr2") on duplicate key update `value` = VALUES(`value`); ";

$res = $medoo->exec($sql);
p($medoo->error());
p($res);

error after execution:

Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column "WEB_SITE_TITLE" in "field list"
)

PDOStatement Object
(
    [queryString] => INSERT INTO t_config (`name`,`value`) VALUES("WEB_SITE_TITLE","11dd11"),("WEB_SITE_KEYWORD","22rr2") on duplicate key update `value`=VALUES(`value`);
)

but when a copy of the statement is run on the command line, the statement is normal.
then consult the relevant documents and find that there is a line of code in the source code in medoo:

// Make MySQL using standard quoted identifier
$commands[] = "SET SQL_MODE=ANSI_QUOTES";

when this line is deleted, it is ready to run.
originally because there are double quotation marks in the statement, syntax checking cannot pass after ANSI_QUOTES is enabled.

the problem that PS: has been perplexing for several days, originally intended to ask questions, did not want to solve in the process of asking questions, but also drunk. It"s like stepping on a medoo pit.

Medoo mysql php

Mar.16,2021

The

test found that SET SQL_MODE=ANSI_QUOTES is not the ultimate cause of sql. Instead, double quotation marks cannot be used to quote strings in the original sql statement.
should use single quotation marks ', or \' with escape characters if the order number cannot be used.

ycdatabase is also a very good database framework. Support stable database connection, C language expansion, high performance. Github address: https://github.com/caohao-php.

< H2 > advantages: < / H2 >

1, Fast-ycdb is a PHP extension written in pure C language mysql database ORM extension, as we all know, database ORM is a very time-consuming operation, especially for interpretative languages such as PHP, and for a project, ORM most cases can account for a large proportion of the project, so here I will MySQL ORM operation C language implementation, the use of C language performance to improve the performance of ORM.

2. Security-ycdb can solve the problem of SQL injection by parameter binding.

3, powerful-convenient function that supports all database operations.

4, simple-very easy to use and learn, with a friendly interface.

5. Data cache-ycdb supports data caching. You can use redis as the medium to cache data in the database. However, remember that when update, insert and delete operations involve data modifications related to cached data, you need to press key to delete your cache to ensure data consistency.

6. Connection pooling-ycdb uses a special way to establish a stable connection pool with MySQL, and the performance can be improved by at least 30%. According to the operating mechanism of PHP, persistent connections can only reside on the worker process after they are established, that is, there are as many long connections as there are working processes. For example, we have 10 PHP servers, each of which starts 1000 PHP-FPM working processes. If they connect to the same MySQL instance, there will be up to 10000 persistent connections on this MySQL instance, which is completely out of control! And the connection pool heartbeat mechanism of PHP is not perfect.

< H2 > directory: < / H2 >

Instruction
Requirement
create a test table
compile ycdb in linux
Start ycdatabase
initialize a ycdb connection
Native SQL execution
error handling
Where statement
Select statement
Insert statement
Replace statement
Update statement
Delete statement
complete example sentence
Database transaction
data cache
PHP database connection pool
Redis connection pool scheme

Previous: React parent-child component communication

Next: Once Mini Program is authorized, will he always maintain the authorized state?

Menu