>我有一个这样的MySQL表,结构如下:
| uri | title |
_________________ _____________
Latest-Update Latest Update
Latest Update
其中 uri 列是索引。我偶尔会在 mysql 表中执行注入,如果我有 1 个或多个同名的 URI,则会出现错误重复条目。所以我使用 PHP 进行插入,并希望在插入时将date("m-d-y")
附加到变量$uri的末尾。我的 INSERT 语句INSERT INTO table(
uri ,
标题) values('$uri', '$title')
.我想看看在插入的表中,在插入之前,我是否可以判断$uri
值是否已经存在于uri
表中,如果是,该变量将变得$uri = $uri."-"date("m-d-y");
我怎么知道?我做了一些研究,让我相信我应该使用INSERT...ON DUPLICATE KEY UPDATE
?
这很可能是由于您的 uri 是主键。为此,您可能应该创建一个代理键,这将允许重复。如果这确实是你需要的。
不确定您是否对触发器感兴趣,但您可以依赖BEFORE INSERT
触发器。触发器需要检查正在插入的新uri
是否具有 dup 条目,如果是,则附加 dateStringOfYourFormat 并插入。
示例 :
mysql> drop table mytable;
Query OK, 0 rows affected (0.03 sec)
mysql> create table mytable( uri varchar(128) );
Query OK, 0 rows affected (0.05 sec)
mysql> drop trigger uri_modifier;
ERROR 1360 (HY000): Trigger does not exist
mysql> delimiter //
mysql> create trigger uri_modifier before insert on mytable
-> for each row
-> begin
-> if ( select count(uri) from mytable where uri=NEW.uri ) > 0 then
-> set @modified_uri_string=concat(NEW.uri, '-', date_add(sysdate(), interval floor(rand()*1e6) microsecond));
-> set NEW.uri=@modified_uri_string;
-> end if;
-> end//
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> set @my_new_uri_string = 'my_uri_string_to_search';
Query OK, 0 rows affected (0.00 sec)
mysql> set @modified_uri_string = @my_new_uri_string;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mytable;
Empty set (0.00 sec)
mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.03 sec)
mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+-------------------------+
| @my_new_uri_string | @modified_uri_string |
+-------------------------+-------------------------+
| my_uri_string_to_search | my_uri_string_to_search |
+-------------------------+-------------------------+
1 row in set (0.00 sec)
mysql> select * from mytable;
+-------------------------+
| uri |
+-------------------------+
| my_uri_string_to_search |
+-------------------------+
1 row in set (0.00 sec)
mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.03 sec)
mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+----------------------------------------------------+
| @my_new_uri_string | @modified_uri_string |
+-------------------------+----------------------------------------------------+
| my_uri_string_to_search | my_uri_string_to_search-2012-05-03 02:21:39.983405 |
+-------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mytable;
+----------------------------------------------------+
| uri |
+----------------------------------------------------+
| my_uri_string_to_search |
| my_uri_string_to_search-2012-05-03 02:21:39.983405 |
+----------------------------------------------------+
2 rows in set (0.02 sec)
mysql> insert into mytable values(@my_new_uri_string);
Query OK, 1 row affected (0.01 sec)
mysql> select @my_new_uri_string, @modified_uri_string;
+-------------------------+----------------------------------------------------+
| @my_new_uri_string | @modified_uri_string |
+-------------------------+----------------------------------------------------+
| my_uri_string_to_search | my_uri_string_to_search-2012-05-03 02:21:39.412580 |
+-------------------------+----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from mytable;
+----------------------------------------------------+
| uri |
+----------------------------------------------------+
| my_uri_string_to_search |
| my_uri_string_to_search-2012-05-03 02:21:39.983405 |
| my_uri_string_to_search-2012-05-03 02:21:39.412580 |
+----------------------------------------------------+
3 rows in set (0.00 sec)
mysql>
mysql>
确保如果插入uri
与传入的内容不同,则有一个句柄来了解修改后的值是什么。