首页 > 代码库 > MySQL存储IP地址操作

MySQL存储IP地址操作

数据库数据表创建语法:

DROP TABLE IF EXISTS `admin`;
CREATE TABLE IF NOT EXISTS `admin`(
    `adminid` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 主键ID,
    `adminuser` VARCHAR(32) NOT NULL DEFAULT ‘‘ COMMENT 管理员账号,
    `adminpass` CHAR(32) NOT NULL DEFAULT ‘‘ COMMENT 管理员密码,
    `adminemail` VARCHAR(50) NOT NULL DEFAULT ‘‘ COMMENT 管理员电子邮箱,
    `logintime` BIGINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 登录时间,
    `loginip` BIGINT NOT NULL DEFAULT 0 COMMENT 登录IP,
    `createtime` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 创建时间,
    PRIMARY KEY(`adminid`),
    UNIQUE admin_adminuser_adminpass(`adminuser`, `adminpass`),
    UNIQUE admin_adminuser_adminemail(`adminuser`, `adminemail`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

(1)mysql控制台下:

ip地址转整型类型inet_aton

mysql> update admin set userip=inet_aton(192.168.1.1) where username=admin;
mysql> select * from admin;
+---------+-----------+----------------------------------+----------------+------------+------------+------------+
| adminid | adminuser | adminpass                        | adminemail     | logintime  | loginip    | createtime |
+---------+-----------+----------------------------------+----------------+------------+------------+------------+
|       1 | admin     | 202cb962ac59075b964b07152d234b70 | zq@zqblogs.cn  | 1482500788 | 3232235777 | 1482419387 |
+---------+-----------+----------------------------------+----------------+------------+------------+------------+
1 row in set (0.00 sec)

 

整型转ip地址类型inet_ntoa

mysql> select inet_ntoa(4294967295);
+-----------------------+
| inet_ntoa(4294967295) |
+-----------------------+
| 255.255.255.255       |
+-----------------------+

 

(2)php代码实现:ip2long

 通过php代码将ip地址插入到数据类型为int或bigint的字段中(使用yii框架的语法)

 $this->updateAll(
  [‘logintime‘ => time(), ‘loginip‘ => ip2long(Yii::$app->request->userIP)],
  ‘adminuser = :user‘,
  [‘:user‘ => $this->adminuser]
);
Yii::$app->request->userIP:表示通过http请求头获取到当前用户浏览器的ip地址(注:可通过$_SERVER[‘REMOTE_ADDR‘]获取到本地ip地址)
(说明1:使用127.0.0.1访问本地apache服务器,而不要用localhost,localhost对应的ip为::1,不符合数据库限制的bigint类型)
(说明2:关于web服务器的ip地址和域名映射关系是在C:\Windows\System32\drivers\etc\hosts文件中配置的

               技术分享

 技术分享

 说明: 转载请注明出处http://www.cnblogs.com/zqblogc/p/6216237.html 

 

友情参考:http://www.qttc.net/201208193.html

MySQL存储IP地址操作