将纯真ip数据库导入MySql

超频化石鱼

于 2021-05-08 11:09:37 发布

阅读量648

点赞数 2

分类专栏： MySql

本文链接：https://blog.csdn.net/fyyyr/article/details/116520884

版权

纯真 ip mysql 数据库查询

MySql 专栏收录该内容

8 篇文章 4 订阅

订阅专栏

ip数据源

网上最多的免费数据源为纯真IP地址数据库。下载一个纯真IP地址数据库应用程序，运行后可看到如下界面：
chunzhen
点击在线升级将数据库升级到最新版。然后点解压，保存到本地。

数据分析

数据保存到本地后是个txt文件，打开，可以看到：

0.0.0.0         0.255.255.255   IANA 保留地址
1.0.0.0         1.0.0.0         美国 亚太互联网络信息中心(CloudFlare节点)
1.0.0.1         1.0.0.1         美国 APNIC&CloudFlare公共DNS服务器
1.0.0.2         1.0.0.255       美国 亚太互联网络信息中心(CloudFlare节点)
1.0.1.0         1.0.3.255       福建省 电信
...
1.4.0.0         1.4.0.255       澳大利亚 APNIC Debogon-prefix网络
...
60.191.240.70   60.191.240.70   浙江省金华市 婺城区广播电视总台

该数据有如下特点：

每行数据有4列：起始ip，截止ip，地点，ISP。
起始ip与截止ip共同构成了一个ip段。起始ip与截止ip可能相同。
含国外地区ip。
含三大运营商外的ISP。
多列之间使用多个数量不确定的空格分隔。
多行使用\r\n分隔。
地点不会包含空格，但ISP会。

为了能导入到MySql数据库中，必须对数据进行处理。
导入MySql，字段之间默认使用制表符\t分隔，结尾使用\r\n分隔。
因此处理该数据应：将起始ip，截止ip，地点，ISP这4列内容之间的空格替换为制表符\t。
注意ISP字段可能会包含空格，故而不能简单地对空格进行替换。

另外，ip中包含.，是个字符串。考虑到查询效率，不建议直接用ip进行比较。因此需要将ip由字符串转换为数字。ip由8×4=32位构成，但这32位是无符号int，数据库的结构默认int是有符号的，无法容纳这么多的位数。故而需要使用MySql的bigint，对应java的long。
故而，创建的MySql表应包含7个字段：

id
ipStart: verchar, 35
ipStartValue: bigint
ipEnd: verchar, 35
ipEndValue: bigint
area: verchar, 255
isp: verchar, 255

处理

处理之前，特别注意导出的txt文件默认使用GB2312编码。因此要么先将文件转换为UTF-8，要么读取时使用GB2312，否则处理后会出现乱码。这里选择读取时使用GB2312。
由上，考虑使用js进行处理。可形成处理思路：

将所有数据放入一个js字符串对象ipData。
对ipData以\r\n进行分割，得到rowArray。rowArray每个元素都是一行原始数据。
对rowArray的每一行进行处理，将空格替换为制表符\t，同时注意不能影响到ISP字段中的空格。
对rowArray处理后的数据进行重新拼接，保存到txt文件中。

其中对rowArray的每一行进行处理：

找到前两处连续空格，将该行数据分割为3部分。
将前两部分转换为int值。
拼接：第一部分 + ‘\t’ + 第一部分数值 + 第二部分 + ‘\t’ + 第二部分数值 + ‘\t’ + 第三部分。
对拼接后的字符串查找第一处空格，将其替换为制表符\t。
在该行数据末尾添加\r\n。

注意处理之前要将本行中所有的"去掉，否则导入时会有干扰。
这样一行数据就处理好了。
另外要注意去掉纯真导出数据最末尾的几行，因为其不符合上述格式。
全部处理完成，将处理结果弹窗提示保存即可。
附源码：

<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<title></title>
		<script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
	</head>
	<body>
		<input type="file" id="file" multiple>
		<button id="btn" class="btn">转换</button>
		<script>			
			var files = []
			window.onload = function(e) {
			    var f = document.getElementById("file")
			    f.onchange = function() {
			        files = this.files
			    }
			}
			
			$("#btn").click(function() {
				var loadCount = 0 // 已读取文件数量
				// 读取单个文件
				var loadAFile = function(file) {
					var reader = new FileReader()
					reader.onload = function(e) {
						ipData = this.result        
						loadCount++
						if (loadCount == files.length) {
							console.log('读取完毕')
						}
						ipData = fieldRow + process(ipData)
						console.log(ipData)
						download('ip数据', ipData)
					}
					reader.readAsText(file, 'GB2312') // 以文本形式读取
				}
				for (var i = 0; i < files.length; i++) {
					loadAFile(files[i])
				}				
			})
			
			/**
			 * 生成txt文件并下载
			 * @param {Object} saveName
			 * @param {Object} txtString
			 */
			var download = function(saveName, txtString) {
				txtString = "\ufeff" + txtString
				var urlObject = window.URL || window.webkitURL || window
				var blob = new Blob([txtString], {type: 'text/plain', charset: 'utf-8'})
				var link = document.createElement("a")
				link.href = urlObject.createObjectURL(blob)
				link.download = saveName
				link.click()
			}
			
			var ipData = ''
			var fieldRow = 'ipStart\tipStartValue\tipEnd\tipEndValue\tarea\tisp\r\n'
			
			/**
			 * 处理ip原始数据
			 * @param {Object} ipData
			 */
			var process = function(ipData) {
				var rowArray = ipData.split('\r\n')
				// 删除最后5行无效数据
				rowArray.splice(rowArray.length-5, 5); 
				var newIpData = ''
				rowArray.forEach(rowText => {
					// 将行中所有的“"”去掉
					rowText = rowText. replace(new RegExp('"','g'), '')
					newIpData += processRow(rowText) + '\r\n'
				})
				return newIpData
			}
			
			/**
			 * 处理单行ip数据
			 * @param {Object} rowText
			 */
			var processRow = function(rowText) {
				rowText = rowText.trim()
				rowText = processIpFields(rowText)
				rowText = processFirstSpace(rowText)
				return rowText
			}
			
			/**
			 * 处理ip字段
			 * @param {Object} text
			 */
			var processIpFields = function(text) {
				var splitArray = splitWithFirstSpace(text)
				if (splitArray.length > 1) {
					var ipStart = splitArray[0]
					var rightArray = splitWithFirstSpace(splitArray[1])
					return ipStart + '\t' + ipToInt(ipStart) + '\t' + rightArray[0] + '\t' + ipToInt(rightArray[0]) + '\t' + rightArray[1]
				} else {
					return text
				}			
			}
			
			/**
			 * 处理第一个空格区域
			 * @param {Object} text
			 */
			var processFirstSpace = function(text) {
				var splitArray = splitWithFirstSpace(text)
				if (splitArray.length > 1) {
					return splitArray[0] + '\t' + splitArray[1]
				} else {
					return text
				}
			}
			
			/**
			 * 按空格区域对text进行分隔
			 * @param {Object} text
			 */
			var splitWithFirstSpace = function(text) {
				var array = []
				var startIndex = text.indexOf(' ')
				if (startIndex > 0) {
					var endIndex = startIndex
					while(text.charAt(endIndex) == ' ') {
						endIndex++
					}
					array.push(text.substring(0, startIndex))
					array.push(text.substring(endIndex, text.length))
				}
				return array
			}
			
			/**
			 * 将ip转为int
			 * @param {Object} ipText
			 */
			var ipToInt = function(ipText) {
				var array = ipText.split('.')
				return (array.length >= 4) ? parseInt(array[0])*16777216 + parseInt(array[1])*65536 + parseInt(array[2])*256 + parseInt(array[3]) : -1
			}
		</script>
	</body>
</html>

导入

使用Navicat打开MySql数据库。
在表上点右键，选：导入向导→文本文件→选择上一步导出的数据文件，然后多次下一步，在字段映射界面确认无问题后，继续下一步，直到开始导入。
fieldMap
等待导入完成即可。

超频化石鱼

关注

2
点赞
踩
1

收藏

觉得还不错? 一键收藏
1
评论
将纯真ip数据库导入MySql

ip数据源网上最多的免费数据源为纯真IP地址数据库。下载一个纯真IP地址数据库应用程序，运行后可看到如下界面：点击在线升级将数据库升级到最新版。然后点解压，保存到本地。数据分析数据保存到本地后是个txt文件，打开，可以看到：0.0.0.0 0.255.255.255 IANA 保留地址1.0.0.0 1.0.0.0 美国亚太互联网络信息中心(CloudFlare节点)1.0.0.1 1.0.0.1 美国 A
复制链接

扫一扫

专栏目录