MySQL创建或更新行与IP? [英] MySql create or update row with ip?

问题描述

我有一个带有ips的表和ip访问某个页面的次数的计数.如果要在ip中存在一行，我想在计数中添加一个，否则，请在ip中创建一行并将计数设置为1.

I have a table with ips and a count of the amount of times the ip accessed a certain page. I want to add one to the count if a row exists with the ip, otherwise create a row with the ip and set the count to 1.

我尝试过

INSERT INTO mytable (ip,count) VALUES ('" + ip + "',1) ON DUPLICATE KEY UPDATE count=count+1;

但是ip不是唯一的或主要的，因此它只会继续创建具有相同ip的行.我尝试使ip唯一，但它不会让我失望.当我尝试创建文本列并使其唯一时，phpmyadmin说BLOB/TEXT column 'test' used in key specification without a key length.

But ip isn't unique or primary, so it just keeps creating rows with the same ip. I tried making ip unique, but it won't let me. When I try to create a text column and make it unique, phpmyadmin says BLOB/TEXT column 'test' used in key specification without a key length.

我可以使用两个语句.我该怎么做?

I'm alright with using two statements. How can I accomplish this?

推荐答案

将ip更改为UNSIGNED INT，在其上创建UNIQUE约束，并使用此约束:

Change ip to UNSIGNED INT, create a UNIQUE constraint on it and use this:

INSERT
INTO    mytable (ip, count)
VALUES  (INET_ATON($ip), 1)
ON DUPLICATE KEY
UPDATE
        count = count + 1

要以点十进制表示法检索IP(例如192.168.1.1)，请使用:

To retrieve the ip in dot-decimal notation (like 192.168.1.1), use:

SELECT  INET_NTOA(ip)
FROM    mytable

您还可以创建一个前缀索引:

You may also create a prefixed index:

CREATE UNIQUE INDEX ux_mytaable_ip ON mytable (ip(15))

，但最好使用INT列存储ipv4地址.

but using INT column to store an ipv4 address is preferred.

这篇关于MySQL创建或更新行与IP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！