php - mysql:将字符串连接到列

Question

我正在尝试向“消息”列添加一条新消息，以便将历史记录保存在一个位置。我可以用新消息更新该字段，但无法连接它们。你能帮忙吗？

这是我的代码:

$stmt = mysqli_prepare($mysqlCon, "INSERT  INTO history (`phone`, `message`) VALUES (?, ?) 
ON DUPLICATE KEY SET message = CONCAT(message,VALUES(message));");

mysqli_stmt_bind_param($stmt, 'ss', $phone, $message);

$phone= $_POST['from'];
$message = $_POST['message'];

编辑:为什么在没有解释的情况下投反对票？

Answer 1

MySQL 5.6.14:

用途:

insert into history (phone, message) 
values (1, 'test') 
on duplicate key 
    update message = concat(message, values(message));

举个例子:

CREATE TABLE `history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone` int(11) DEFAULT NULL,
  `message` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB;

让我们添加初始数据:

insert into history (phone, message) values (1, 'test');

+----+-------+---------+
| id | phone | message |
+----+-------+---------+
|  1 |     1 | test    |
+----+-------+---------+

让我们使用 set 单词通过on重复键再次添加它:

insert into history (phone, message) values (1, 'test') on duplicate key set message = concat(message, values(message));

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set message = concat(message, values(message))' at line 1

让我们使用更新单词通过重复键再次添加它:

insert into history (phone, message) 
values (1, 'test') 
on duplicate key 
     update message = concat(message, values(message));

Query OK, 2 rows affected (0.01 sec)

+----+-------+----------+
| id | phone | message  |
+----+-------+----------+
|  1 |     1 | testtest |
+----+-------+----------+

希望这个例子有帮助。