我应该如何正确存储和检索IP? [英] How should I properly store and retrieve IPs?

问题描述

我有表 Users ，其中的一列 user 类型为 VARCHAR(15) .我可以在其中插入一个句点:

I have table Users with one column user of type VARCHAR(15). I can insert a period into it:

INSERT INTO `LJ`.`Users` (`user`) VALUES ('.')

现在我要搜索这段时间:

Now I want to search for this period:

SELECT * FROM `Users` WHERE `user` LIKE '.'

或

SELECT * FROM `Users` WHERE `user` = '.'

在两种情况下，都找不到现有条目:

In both cases, the existing entry is not found:

MySQL返回了一个空结果集(即零行). (查询耗时0.0001秒)

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0001 sec)

同样的逗号问题.

实际上，我的意图是存储125.50.75.80之类的IP地址，但是我将问题归结为无法搜索标点符号.我试过了 '\.'也无济于事.

Actually, my intention is to store IP addresses like 125.50.75.80, but I've boiled the problem down to inability to search for a punctuation sign. I tried '\.' also to no avail.

这是文字的问题；如何处理包含带句点的字符串的变量?

And this is a problem with literals; what to do with variables, containing strings with periods?

更新:我已经在使用表的命令行中创建的数据库中进行了测试:

UPDATE: I've tested in a database created in the command line with table:

CREATE TABLE LJ.Users ( user VARCHAR(15) );

，一切正常.我怀疑在使用phpMyAdmin创建的数据库中出了点问题:

and everything works OK. I suspect something is wrong in my database created with phpMyAdmin:

推荐答案

VARCHAR列的当前问题是您使用的armscii8_general_ci排序规则类型，该类型将转换一些存储的字符(例如逗号)和点.

The current issue with your VARCHAR column is the collate type you're using armscii8_general_ci which converts some of the stored characters such as comma and dot.

在此处查看示例.

也许您打算使用utf8_general_ci会产生想要的结果:

Perhaps you meant to use utf8_general_ci which would yield your desired results:

在此处查看示例.

不要使用VARCHAR存储IPv4，请使用INT unsigned，然后可以使用

Do not use VARCHAR to store IPv4, use INT unsigned and then you can use INET_ATON() and INET_NTOA() functions to return/insert/compare the IPv4.

CREATE TABLE users
(
    user_ip INT unsigned
);

然后插入要使用的IPv4:

Then to insert an IPv4 you would use:

INSERT INTO users (user_ip) VALUES (INET_ATON('125.50.75.80'));

那么您可以这样阅读:

SELECT INET_NTOA(user_ip) FROM users WHERE user_ip = INET_ATON('125.50.75.80')

实时演示.

这篇关于我应该如何正确存储和检索IP?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！