我有一个URL表,我不想要任何重复的URL.如何使用PHP/MySQL检查表中是否已存在给定的URL？
如果您不想要重复,可以执行以下操作:
添加唯一性约束
使用" REPLACE "或" INSERT ... ON DUPLICATE KEY UPDATE "语法
如果多个用户可以向DB插入数据,@ Jeremy Ruten建议的方法可能会导致错误:执行检查后,有人可以向表中插入类似的数据.
要回答您的初始问题,检查是否存在重复的最简单方法是针对您要添加的内容运行SQL查询!
例如,如果您想检查http://www.example.com/表中的url links,那么您的查询看起来就像
SELECT * FROM links WHERE url = 'http://www.example.com/';
你的PHP代码看起来像
$conn = mysql_connect('localhost', 'username', 'password');
if (!$conn)
{
die('Could not connect to database');
}
if(!mysql_select_db('mydb', $conn))
{
die('Could not select database mydb');
}
$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);
if (!$result)
{
die('There was a problem executing the query');
}
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows > 0)
{
die('This URL already exists in the database');
}
我已经在这里写了这个,所有连接到数据库,等等.你可能已经连接到数据库了,所以你应该使用它而不是启动一个新的连接($conn在mysql_query命令和删除要做的事情mysql_connect和mysql_select_db)
当然,还有其他方式连接到数据库,如PDO,或使用ORM,或类似的,所以如果你已经使用这些,这个答案可能不相关(并且它可能有点超出范围给出与此相关的答案!)
但是,MySQL提供了许多方法来防止这种情况发生.
首先,您可以将字段标记为"唯一".
假设我有一个表格,我只想存储从我的网站链接到的所有网址,以及他们上次访问的网址.
我的定义可能如下所示: -
CREATE TABLE links
(
url VARCHAR(255) NOT NULL,
last_visited TIMESTAMP
)
这将允许我一遍又一遍地添加相同的URL,除非我写了一些类似于上面的PHP代码来阻止这种情况发生.
但是,我的定义是改为
CREATE TABLE links ( url VARCHAR(255) NOT NULL, last_visited TIMESTAMP, PRIMARY KEY (url) )
然后,当我尝试两次插入相同的值时,这会使mysql抛出错误.
PHP中的一个例子是
$result = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);
if (!$result)
{
die('Could not Insert Row 1');
}
$result2 = mysql_query("INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW()", $conn);
if (!$result2)
{
die('Could not Insert Row 2');
}
如果你运行它,你会发现在第一次尝试时,脚本会因注释而死亡Could not Insert Row 2.然而,在随后的运行中,它会死亡Could not Insert Row 1.
这是因为MySQL知道url是表的主键.主键是该行的唯一标识符.大多数情况下,将行的唯一标识符设置为数字很有用.这是因为MySQL查找数字比查找文本更快.在MySQL中,密钥(特别是主密钥)用于定义两个表之间的关系.例如,如果我们有一个用户表,我们可以将其定义为
CREATE TABLE users ( username VARCHAR(255) NOT NULL, password VARCHAR(40) NOT NULL, PRIMARY KEY (username) )
但是,当我们想要存储有关用户发布的帖子的信息时,我们必须存储该帖子的用户名,以确定该帖子属于该用户.
我已经提到MySQL在查找数字方面比在字符串方面更快,所以这意味着我们会花时间查找字符串,而不需要.
要解决这个问题,我们可以添加一个额外的列user_id,并将其作为主键(因此,当根据帖子查找用户记录时,我们可以更快地找到它)
CREATE TABLE users ( user_id INT(10) NOT NULL AUTO_INCREMENT, username VARCHAR(255) NOT NULL, password VARCHAR(40) NOT NULL, PRIMARY KEY (`user_id`) )
你会注意到我在这里添加了一些新东西 - AUTO_INCREMENT.这基本上允许我们让该领域照顾自己.每次插入一个新行时,它会将前一个数字加1并存储,因此我们不必担心编号,并且可以让它自己执行此操作.
所以,通过上表,我们可以做类似的事情
INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
然后
INSERT INTO users (username, password) VALUES('User', '988881adc9fc3655077dc2d4d757d480b5ea0e11');
当我们从数据库中选择记录时,我们得到以下结果: -
mysql> SELECT * FROM users; +---------+----------+------------------------------------------+ | user_id | username | password | +---------+----------+------------------------------------------+ | 1 | Mez | d3571ce95af4dc281f142add33384abc5e574671 | | 2 | User | 988881adc9fc3655077dc2d4d757d480b5ea0e11 | +---------+----------+------------------------------------------+ 2 rows in set (0.00 sec)
但是,这里 - 我们有一个问题 - 我们仍然可以添加另一个用户名相同的用户!显然,这是我们不想做的事情!
mysql> SELECT * FROM users; +---------+----------+------------------------------------------+ | user_id | username | password | +---------+----------+------------------------------------------+ | 1 | Mez | d3571ce95af4dc281f142add33384abc5e574671 | | 2 | User | 988881adc9fc3655077dc2d4d757d480b5ea0e11 | | 3 | Mez | d3571ce95af4dc281f142add33384abc5e574671 | +---------+----------+------------------------------------------+ 3 rows in set (0.00 sec)
让我们改变我们的表定义!
CREATE TABLE users ( user_id INT(10) NOT NULL AUTO_INCREMENT, username VARCHAR(255) NOT NULL, password VARCHAR(40) NOT NULL, PRIMARY KEY (user_id), UNIQUE KEY (username) )
让我们看看当我们现在尝试将同一个用户插入两次时会发生什么.
mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO users (username, password) VALUES('Mez', 'd3571ce95af4dc281f142add33384abc5e574671');
ERROR 1062 (23000): Duplicate entry 'Mez' for key 'username'
好哇!我们现在尝试第二次插入用户名时出现错误.使用类似上面的内容,我们可以在PHP中检测到这一点.
现在,让我们回到我们的链接表,但有一个新的定义.
CREATE TABLE links
(
link_id INT(10) NOT NULL AUTO_INCREMENT,
url VARCHAR(255) NOT NULL,
last_visited TIMESTAMP,
PRIMARY KEY (link_id),
UNIQUE KEY (url)
)
然后让我们将"http://www.example.com"插入数据库.
INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
如果我们再尝试插入它......
ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'
但是如果我们想要更新上次访问的时间会怎样？
好吧,我们可以用PHP做一些复杂的事情,如下:
$result = mysql_query("SELECT * FROM links WHERE url = 'http://www.example.com/'", $conn);
if (!$result)
{
die('There was a problem executing the query');
}
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows > 0)
{
$result = mysql_query("UPDATE links SET last_visited = NOW() WHERE url = 'http://www.example.com/'", $conn);
if (!$result)
{
die('There was a problem updating the links table');
}
}
或者,甚至抓住数据库中行的id并使用它来更新它.
$ result = mysql_query("SELECT*FROM links WHERE url ='http://www.example.com/'",$ conn);
if (!$result)
{
die('There was a problem executing the query');
}
$number_of_rows = mysql_num_rows($result);
if ($number_of_rows > 0)
{
$row = mysql_fetch_assoc($result);
$result = mysql_query('UPDATE links SET last_visited = NOW() WHERE link_id = ' . intval($row['link_id'], $conn);
if (!$result)
{
die('There was a problem updating the links table');
}
}
但是,MySQL有一个很好的内置功能叫做 REPLACE INTO
让我们看看它是如何工作的.
mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url | last_visited |
+---------+-------------------------+---------------------+
| 1 | http://www.example.com/ | 2011-08-19 23:48:03 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)
mysql> INSERT INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
ERROR 1062 (23000): Duplicate entry 'http://www.example.com/' for key 'url'
mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www.example.com/', NOW());
Query OK, 2 rows affected (0.00 sec)
mysql> SELECT * FROM links;
+---------+-------------------------+---------------------+
| link_id | url | last_visited |
+---------+-------------------------+---------------------+
| 2 | http://www.example.com/ | 2011-08-19 23:55:55 |
+---------+-------------------------+---------------------+
1 row in set (0.00 sec)
请注意,在使用时REPLACE INTO,它会更新last_visited时间,而不会抛出错误!
这是因为MySQL检测到您正在尝试替换行.它知道您想要的行,因为您已将url设置为唯一.MySQL通过使用您传入的位应该是唯一的(在本例中为url)并为该行更新其他值来计算要替换的行.它还更新了link_id - 这有点出乎意料!(事实上,我没有意识到这会发生,直到我看到它发生!)
但是如果你想添加一个新的URL呢？好吧,REPLACE INTO如果找不到匹配的唯一行,将很乐意插入新行!
mysql> REPLACE INTO links (url, last_visited) VALUES ('http://www.stackoverflow.com/', NOW());
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM links;
+---------+-------------------------------+---------------------+
| link_id | url | last_visited |
+---------+-------------------------------+---------------------+
| 2 | http://www.example.com/ | 2011-08-20 00:00:07 |
| 3 | http://www.stackoverflow.com/ | 2011-08-20 00:01:22 |
+---------+-------------------------------+---------------------+
2 rows in set (0.00 sec)
我希望这能回答您的问题,并为您提供有关MySQL如何工作的更多信息!
您是否完全关注与完全相同的字符串的URL ...如果是这样,在其他答案中有很多好的建议.或者你还要担心经典化吗？
例如:http://google.com和http://go%4fgle.com是完全相同的网址,但只允许使用任何数据库技术作为重复网址.如果这是一个问题,您应该预处理URL以解析和字符转义序列.
根据URL的来源,您还必须担心参数以及它们在您的应用程序中是否重要.
首先,准备数据库.
域名不区分大小写,但您必须假设URL的其余部分.(并非所有的Web服务器都遵循URL中的大小写,但大多数都是这样,并且您无法通过查看来轻松判断.)
假设您需要存储多个域名,请使用区分大小写的排序规则.
如果您决定将URL存储在两列中 - 一列用于域名,另一列用于资源定位器 - 请考虑对域名使用不区分大小写的排序规则,并为资源定位器使用区分大小写的排序规则.如果我是你,我会测试两种方式(一列中的URL与两列中的URL).
在URL列上放置UNIQUE约束.或者在列对上,如果将域名和资源定位符存储在单独的列中,则为UNIQUE (url, resource_locator).
使用CHECK()约束将编码的URL保留在数据库之外.此CHECK()约束对于防止错误数据通过批量复制或通过SQL shell进入是必不可少的.
其次,准备URL.
域名不区分大小写.如果将完整URL存储在一列中,请在所有URL上小写域名.但请注意,某些语言的大写字母没有小写等效字母.
考虑修剪尾随字符.例如,amazon.com的这两个网址指向同一产品.您可能想要存储第二个版本,而不是第一个版本.
http://www.amazon.com/Systemantics-Systems-Work-Especially-They/dp/070450331X/ref=sr_1_1?ie=UTF8&qid=1313583998&sr=8-1
http://www.amazon.com/Systemantics-Systems-Work-Especially-They/dp/070450331X
解码编码的URL.(请参阅php的urldecode()函数.请仔细注意其缺点,如该页面的注释中所述.)就个人而言,我宁愿在数据库中而不是在客户端代码中处理这些类型的转换.这将涉及撤消对表和视图的权限,并允许仅通过存储过程进行插入和更新; 存储过程处理将URL放入规范形式的所有字符串操作.但是,当你尝试时,请注意性能.CHECK()约束(见上文)是您的安全网.
第三,如果您只插入URL,请不要先测试它的存在.相反,尝试插入并捕获如果值已存在您将获得的错误.对于每个新URL,测试和插入会对数据库执行两次命中.插入和陷阱只需命中一次数据库.请注意,insert-and-trap与insert-and-ignore-errors不同.只有一个特定错误意味着您违反了唯一约束; 其他错误意味着还有其他问题.
另一方面,如果您将URL与其他一些数据一起插入同一行,则需要提前决定是否要处理重复的URL
删除旧行并插入新行(请参阅MySQL的REPLACE扩展到SQL)
更新现有值(请参阅ON DUPLICATE KEY UPDATE)
无视这个问题
要求用户采取进一步行动
REPLACE消除了捕获重复键错误的需要,但如果存在外键引用,则可能会产生不幸的副作用.
要保证唯一性,您需要添加唯一约束.假设您的表名为"urls"且列名为"url",则可以使用此alter table命令添加唯一约束:
alter table urls add constraint unique_url unique (url);
如果您已经在表中已经有重复的URL,则alter table可能会失败(谁真的知道MySQL).
简单的SQL解决方案需要一个独特的领域; 逻辑解决方案没有.
您应该规范化您的网址,以确保没有重复.PHP中的函数,例如strtolower()和urldecode()或rawurldecode().
假设:您的表名称为"网站",您网址的列名称为"网址",与网址关联的任意数据位于"数据"列中.
Logic Solutions
SELECT COUNT(*) AS UrlResults FROM websites WHERE url='http://www.domain.com'
使用SQL或PHP中的if语句测试上一个查询,以确保在继续INSERT语句之前它为0.
简单的SQL语句
场景1:您的数据库是先到先得的表,您不希望将来有重复的条目.
ALTER TABLE websites ADD UNIQUE (url)
如果该列中已存在url值,这将阻止任何条目能够输入到数据库中.
场景2:您希望获得每个URL的最新信息,并且不希望复制内容.这种情况有两种解决方案.(这些解决方案还要求'url'是唯一的,因此还需要执行场景1中的解决方案.)
REPLACE INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')
如果存在行,并且在所有情况下都是INSERT,则会触发DELETE操作,因此请小心使用ON DELETE声明.
INSERT INTO websites (url, data) VALUES ('http://www.domain.com', 'random data')
ON DUPLICATE KEY UPDATE data='random data'
如果存在行,则会触发UPDATE操作,如果不存在,则触发INSERT.
京公网安备 11010802040832号 | 京ICP备19059560号-6 