开发参考

多语言解析以及导入数据库脚本

https://github.com/zengzhan/qqzeng-ip

https://gitee.com/qqzeng-ip/qqzeng-ip

https://gitcode.com/qqzeng-ip/qqzeng-ip


IP地址数据库 MySQL 


--MySql导入方法:

--创建 IP地址数据库表

--字段 ip段 数字段 大洲 国家 省份 城市 县区 运营商 区划代码 国家英文名称 国家简码 经度 纬度

CREATE TABLE `ip`.`ip` (
`ip_start` VARCHAR(45) NULL,
`ip_end` VARCHAR(45) NULL,
`ip_start_num` BIGINT NULL,
`ip_end_num` BIGINT NULL,
`continent` VARCHAR(45) NULL,
`country` VARCHAR(45) NULL,
`province` VARCHAR(45) NULL,
`city` VARCHAR(45) NULL,
`district` VARCHAR(45) NULL,
`isp` VARCHAR(45) NULL,
`area_code` VARCHAR(45) NULL,
`country_english` VARCHAR(45) NULL,
`country_code` VARCHAR(45) NULL,
`longitude` VARCHAR(45) NULL,
`latitude` VARCHAR(45) NULL
);


--导入数据库
LOAD DATA LOCAL INFILE 'G:\IP数据库\ip.txt'
INTO TABLE ip
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n' --Windows/Apple 用'\r\n',  Unix/Linux用'\n'
(ip_start, ip_end, ip_start_num,ip_end_num,continent,country,province,
     city,district,isp,area_code,country_english,country_code,longitude,latitude);

--查询
SELECT * FROM ip WHERE INET_ATON('219.232.57.199') 
     BETWEEN ip_start_num AND ip_end_num LIMIT 1


--mysql乱码如何解决？

--确保两者编码统一 才不会乱码
--（1）先将txt文件转换为UTF-8格式
--（2）导入命令中加入character set utf8
   into table test character set utf8 fields……

IP地址数据库 MS-SQL


--附 MSSQL导入方法:

--创建 最新IP地址数据库表

--字段 ip段 数字段 大洲 国家 省份 城市 县区 运营商 区划代码 国家英文名称 国家简码 经度 纬度

CREATE TABLE [dbo].[ip](
[ip_start] [varchar](50) NULL,
[ip_end] [varchar](50) NULL,
[ip_start_num] [bigint] NULL,
[ip_end_num] [bigint] NULL,
[continent] [varchar](50) NULL,
[country] [varchar](50) NULL,
[province] [varchar](50) NULL,
[city] [varchar](50) NULL,
[district] [varchar](50) NULL,
[isp] [varchar](50) NULL,
[area_code] [varchar](50) NULL,
[country_english] [varchar](50) NULL,
[country_code] [varchar](50) NULL,
[longitude] [varchar](50) NULL,
[latitude] [varchar](50) NULL
)


--导入数据库
BULK INSERT dbo.[ip]
FROM 'G:\IP数据库\ip.txt'
WITH (
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)


--查询
SELECT  * FROM dbo.ip WHERE @num BETWEEN StartNum AND EndNum

手机号段数据库 MySQL


--mysql 导入数据库

--创建表 最新手机号段归属地数据库

--字段：前三位 号段 省份 城市 运营商类型  邮编 区号 行政区划编码

CREATE TABLE `phone`.`phone` (
`pref`  VARCHAR(45) NULL,
`phone` VARCHAR(45) NULL,
`province` VARCHAR(45) NULL,
`city` VARCHAR(45) NULL,
`isp` VARCHAR(45) NULL,
`post_code` VARCHAR(45) NULL,
`city_code` VARCHAR(45) NULL,
`area_code` VARCHAR(45) NULL
);

#导入txt到mysql

load data local infile 'c:/phone.txt'
into table phone
fields terminated by '\t'
lines terminated by '\r\n'
(pref, phone, province,city,isp,post_code,city_code,area_code);

#导入csv到mysql

load data local infile 'c:/phone.csv'
into table  phone
fields terminated by ','
optionally enclosed by '"'
escaped by '"'
lines terminated by '\r\n'
(pref, phone, province,city,isp,post_code,city_code,area_code);


--默认 txt为中文编码  导入时 请转为utf-8编码 以免乱码

--mysql乱码如何解决？

--解决：

--确保两者编码统一 才不会乱码

  --（1）先将txt文件转换为UTF-8格式

  --（2）导入命令中加入character set utf8

  --如：
      --into table test character set utf8 fields……


--The used command is not allowed with this MySQL version

[mysqld]        #服务端配置
local-infle = 1

[mysql]         #客户端配置
local-infile = 1

手机号段数据库 MS-SQL


--附 MSSQL导入方法:

--创建表 最新手机号段归属地数据库

--字段：前三位 号段 省份 城市 运营商类型  邮编 区号 行政区划代码

CREATE TABLE [dbo].[PhoneLocation](
[pref] [varchar](50) NULL,
[phone] [varchar](50) NULL,
[province] [varchar](50) NULL,
[city] [varchar](50) NULL,
[isp] [varchar](50) NULL,
[post_code] [varchar](50) NULL,
[city_code] [varchar](50) NULL,
[area_code] [varchar](50) NULL
)

--导入数据库
BULK INSERT dbo.[PhoneLocation]
FROM 'G:\IP数据库\PhoneLocation.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)


--查询
SELECT  * FROM dbo.PhoneLocation WHERE phone='1886999'

行政区划数据库 MySQL


--mysql 导入数据库

--创建表 最新行政区划数据库 三级 旗舰版

--字段
--区划ID
--父ID
--级别
--全称
--简称
--ID路径（110000,110100,110105）
--省份全称
--城市全称
--县区全称
--省份简称
--城市简称
--县区简称
--省份拼音
--城市拼音
--县区拼音
--拼音
--简拼
--首拼
--区号
--邮编
--经度
--纬度
--是否行政区
--类型（县级市|地级市|经济开发区|高新区|新区）


CREATE TABLE `areas` (
`ID` varchar(45) DEFAULT NULL,
`ParentId` varchar(45) DEFAULT NULL,
`LevelType` varchar(45) DEFAULT NULL,
`Name` varchar(45) DEFAULT NULL,
`ShortName` varchar(45) DEFAULT NULL,
`ParentPath` varchar(45) DEFAULT NULL,
`Province` varchar(45) DEFAULT NULL,
`City` varchar(45) DEFAULT NULL,
`District` varchar(45) DEFAULT NULL,
`ProvinceShortName` varchar(45) DEFAULT NULL,
`CityShortName` varchar(45) DEFAULT NULL,
`DistrictShortName` varchar(45) DEFAULT NULL,
`ProvincePinyin` varchar(45) DEFAULT NULL,
`CityPinyin` varchar(45) DEFAULT NULL,
`DistrictPinyin` varchar(150) DEFAULT NULL,
`CityCode` varchar(45) DEFAULT NULL,
`ZipCode` varchar(45) DEFAULT NULL,
`Pinyin` varchar(150) DEFAULT NULL,
`Jianpin` varchar(45) DEFAULT NULL,
`FirstChar` varchar(45) DEFAULT NULL,
`lng` varchar(45) DEFAULT NULL,
`Lat` varchar(45) DEFAULT NULL,
`Remark1` varchar(45) DEFAULT NULL,
`Remark2` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


--省市区街道乡镇 四级 旗舰版

CREATE TABLE `areasTown` (
`ID` varchar(45) DEFAULT NULL,  --行政区划编码
`ParentId` varchar(45) DEFAULT NULL,  --父子级联
`LevelType` varchar(45) DEFAULT NULL,  --级别
`Name` varchar(45) DEFAULT NULL,  --名称
`ParentPath` varchar(45) DEFAULT NULL, --组合路径
`Province` varchar(45) DEFAULT NULL,  --省份
`City` varchar(45) DEFAULT NULL,  --城市
`District` varchar(45) DEFAULT NULL,--县区
`Town` varchar(45) DEFAULT NULL,  --街道/乡镇
`Pinyin` varchar(150) DEFAULT NULL,	--拼音
`Jianpin` varchar(45) DEFAULT NULL,	--简拼
`FirstChar` varchar(45) DEFAULT NULL,--首字母
`CityCode` varchar(45) DEFAULT NULL,--区号
`ZipCode` varchar(45) DEFAULT NULL,--邮编
`lng` varchar(45) DEFAULT NULL,--经度
`Lat` varchar(45) DEFAULT NULL  --纬度
)


--省市区街道乡镇村 五级 基础版

CREATE TABLE `areasVillage` (
`ID` varchar(45) DEFAULT NULL,  --行政区划编码
`ParentId` varchar(45) DEFAULT NULL,  --父子级联
`LevelType` varchar(45) DEFAULT NULL,  --级别
`Name` varchar(45) DEFAULT NULL,  --名称
`Province` varchar(45) DEFAULT NULL,  --省份
`City` varchar(45) DEFAULT NULL,  --城市
`District` varchar(45) DEFAULT NULL,--县区
`Town` varchar(45) DEFAULT NULL,  --街道/乡镇
`Village` varchar(45) DEFAULT NULL,	--社区/村
`ParentPath` varchar(100) DEFAULT NULL, --组合路径
)

-- 省市区 历史旗舰版
CREATE TABLE `areasHistory` (
`ID` varchar(45) DEFAULT NULL, --行政区划编码
`ParentId` varchar(45) DEFAULT NULL, --父子级联
`LevelType` varchar(45) DEFAULT NULL, --级别
`Name` varchar(45) DEFAULT NULL, --名称
`Province` varchar(45) DEFAULT NULL,  --省份
`City` varchar(45) DEFAULT NULL,  --城市
`District` varchar(45) DEFAULT NULL,--县区
`Remark` varchar(45) DEFAULT NULL, --是否撤销
`NewID` varchar(45) DEFAULT NULL,--最新行政编码	
`NewAddress` varchar(150) DEFAULT NULL,--当前省市区聚合
`NewShort` varchar(150) DEFAULT NULL,--当前省市区简称聚合	
`YearTime` varchar(45) DEFAULT NULL  --变更年份
)



-- 省市区 三级基础版
CREATE TABLE `areasBase` (
`ID` varchar(45) DEFAULT NULL, 
`ParentId` varchar(45) DEFAULT NULL, 
`LevelType` varchar(45) DEFAULT NULL,
`Name` varchar(45) DEFAULT NULL, 
`ShortName` varchar(45) DEFAULT NULL, 
`ProvinceShortName` varchar(45) DEFAULT NULL, 
`CityShortName` varchar(45) DEFAULT NULL, 
`DistrictShortName` varchar(45) DEFAULT NULL,
`CityCode` varchar(45) DEFAULT NULL,
`ZipCode` varchar(45) DEFAULT NULL,
`Remark1` varchar(45) DEFAULT NULL,
`Remark2` varchar(45) DEFAULT NULL
);


-- 最后一行 空行
load data  infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/areasBase.txt'
into table areasBase
fields terminated by '\t'  -- '|'
lines terminated by '\r\n'
(ID,ParentId,LevelType,Name,ShortName,ProvinceShortName,CityShortName,
    DistrictShortName, CityCode,ZipCode,Remark1,Remark2 );


-- 导入数据库

-- 省市区三级旗舰版
load data local infile 'C:\ProgramData\MySQL\areas.txt'
into table areas
fields terminated by '\t'  -- '|'
lines terminated by '\n'
(ID,ParentId,LevelType,Name,ShortName,ParentPath,Province,City,District,
    ProvinceShortName,CityShortName,DistrictShortName,ProvincePinyin,CityPinyin,
    DistrictPinyin,Pinyin,Jianpin,FirstChar,CityCode,ZipCode, lng,Lat,Remark1,Remark2);

-- 省市区镇四级旗舰版
load data local infile 'D:\areasTown.txt'
into table areasTown
fields terminated by '\t'  -- '|'
lines terminated by '\n'
(ID,ParentId,LevelType,Name,ParentPath,Province,City,District,Town,
    Pinyin,Jianpin,FirstChar,CityCode,ZipCode,Lng,Lat);

-- 省市区镇村五级基础版
load data local infile 'D:\AreasVillage.txt'
into table areasVillage
fields terminated by '\t'  -- '|'
lines terminated by '\n'
(ID,ParentId,LevelType,Name,Province,City,District,Town,Village,ParentPath);

-- 省市区历史旗舰版
load data  infile 'D:\areasHistory.txt'
into table areasHistory
fields terminated by '\t'  -- '|'
lines terminated by '\r\n'
(ID,ParentId,LevelType,Name,Province,City,District,Remark,NewID,NewAddress,NewShort,YearTime);

-- Error Code: 1148. The used command is not allowed with this MySQL version
-- local-infile = 1 修改后必须重启mysql

--错误 Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access.  
--解决：load data local infile  ->  load data  infile 

-- xls 全选复制到txt
-- 默认 txt为中文编码  导入时 请转为utf-8编码 以免乱码

行政区划数据库 MS-SQL


--附 MSSQL导入方法:

--创建 最新行政区划数据库 三级 旗舰版

--字段 区划ID-父ID-级别-全称-简称-拼音-简拼-首拼-区号-邮编-经度-纬度-备注-类型-
 --ID路径-省份全称-城市全称-县区全称-省份简称-城市简称-县区简称-省份拼音-城市拼音-县区拼音

CREATE TABLE [dbo].[areas](
[ID] [nvarchar](50) NULL,		--区划ID
[ParentId] [nvarchar](50) NULL,		--父ID
[LevelType] [nvarchar](50) NULL,	--级别
[Name] [nvarchar](50) NULL,		--全称
[ShortName] [nvarchar](50) NULL,	--简称
[ParentPath] [nvarchar](50) NULL,	--ID路径（110000,110100,110105）
[Province] [nvarchar](50) NULL,		--省份全称
[City] [nvarchar](50) NULL,		--城市全称
[District] [nvarchar](50) NULL,		--县区全称
[ProvinceShortName] [nvarchar](50) NULL,--省份简称
[CityShortName] [nvarchar](50) NULL,	--城市简称
[DistrictShortName] [nvarchar](50) NULL,--县区简称
[ProvincePinyin] [nvarchar](50) NULL,	--省份拼音
[CityPinyin] [nvarchar](50) NULL,	--城市拼音
[DistrictPinyin] [nvarchar](150) NULL,	--县区拼音
[Pinyin] [nvarchar](150) NULL,		--拼音
[Jianpin] [nvarchar](50) NULL,		--简拼
[FirstChar] [nvarchar](50) NULL,	--首拼
[CityCode] [nvarchar](50) NULL,		--区号
[ZipCode] [nvarchar](50) NULL,		--邮编
[Lng] [nvarchar](50) NULL,		--经度
[Lat] [nvarchar](50) NULL,		--纬度
[Remark1] [nvarchar](50) NULL,		--是否行政区
[Remark2] [nvarchar](50) NULL,		--类型（县级市|地级市|经济开发区|高新区|新区）
) ON [PRIMARY]



--导入数据库   txt 中文编码
BULK INSERT dbo.[areas]
FROM 'G:\db\areas.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)


--查询
SELECT  * FROM dbo.areas


--省市区街道乡镇 四级 旗舰版

CREATE TABLE [dbo].[areasTown](
[AreaId] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,  --行政区划编码
[ParentId] [int] NULL,  --父子级联
[LevelType] [int] NULL,  --级别
[Name] [nvarchar](50) NULL,  --名称
[ParentPath] [nvarchar](50) NULL,--组合路径
[Province] [nvarchar](50) NULL,	--省份
[City] [nvarchar](50) NULL,  --城市
[District] [nvarchar](50) NULL,	--县区
[Town] [nvarchar](50) NULL,  --街道/乡镇
[Pinyin] [nvarchar](200) NULL,	--拼音
[Jianpin] [nvarchar](50) NULL,	--简拼
[FirstChar] [nvarchar](50) NULL,--首字母
[CityCode] [nvarchar](50) NULL,	--区号
[ZipCode] [nvarchar](50) NULL,	--邮编
[Lng] [nvarchar](50) NULL,  --经度
[Lat] [nvarchar](50) NULL,  --纬度
)

--省市区街道乡镇村 五级 基础版

CREATE TABLE [dbo].[areasVillage](
[AreaId] [int] IDENTITY(1,1) NOT NULL,
[ID] [int] NULL,  --行政区划编码
[ParentId] [int] NULL,  --父子级联
[LevelType] [int] NULL,  --级别
[Name] [nvarchar](50) NULL,  --名称
[Province] [nvarchar](50) NULL,	--省份
[City] [nvarchar](50) NULL,  --城市
[District] [nvarchar](50) NULL,	--县区
[Town] [nvarchar](50) NULL,  --街道/乡镇
[Village] [nvarchar](50) NULL,	--社区/村
[ParentPath] [nvarchar](100) NULL,--组合路径
)

--省市区 历史旗舰版
CREATE TABLE [dbo].[areasHistory](
[ID] [int] NULL,  --行政区划编码
[ParentId] [int] NULL, --父子级联
[LevelType] [int] NULL,  --级别
[Name] [nvarchar](50) NULL, --名称
[Province] [nvarchar](50) NULL, --省份
[City] [nvarchar](50) NULL,  --城市
[District] [nvarchar](50) NULL,--县区
[Remark] [nvarchar](50) NULL, --是否撤销
[NewID] [int] NULL, --最新行政编码
[NewAddress] [nvarchar](150) NULL,--最新省市区聚合
[NewShort] [nvarchar](150) NULL,--最新省市区简称聚合	
[YearTime] [int] NULL --变更年份
)

通用数据库导入方法



 -- MySQL SqlServer PostgreSQL Oracle MongoDB 等 数据库 导入参考
 
 -- 常用选项,如编码、字段分隔符、行分隔符、跳过第一行字段名等 
 
 -- 格式:csv|txt; 
 -- 编码:UTF-8|中文编码;
 -- 字段分隔符: \t  |  , 等; 
 -- 换行符\n还是\r\n等。在 Windows,通常使用 `\r\n`， Linux/Unix ，通常使用 `\n` 作为换行符
 -- 知否跳过 第一行字段信息  



MySQL:
```sql
LOAD DATA [LOCAL] INFILE '/path/to/file.csv' 
INTO TABLE table_name
CHARACTER SET utf8
FIELDS TERMINATED BY ',' -- 字段分隔符
OPTIONALLY ENCLOSED BY '"' -- 字段包含引号时需要设置
ESCAPED BY '\\' -- 转义符号
LINES TERMINATED BY '\n' -- 行分隔符
IGNORE 1 ROWS; -- 忽略第一行字段名
```

SQL Server:
```sql
BULK INSERT table_name
FROM '/path/to/file.csv'
WITH (
    CODEPAGE = '65001', -- UTF-8 编码
    FIELDTERMINATOR = ',', -- 字段分隔符
    ROWTERMINATOR = '\n', -- 行分隔符
    FIRSTROW = 2 -- 忽略第一行字段名
);
```

PostgreSQL:
```sql
COPY table_name 
FROM '/path/to/file.csv' 
WITH (
    FORMAT CSV,
    ENCODING 'UTF8', -- 编码
    DELIMITER ',', -- 字段分隔符
    NULL '', -- 空字符串表示NULL
    HEADER -- 忽略第一行字段名
);
```

Oracle:
```sql
CREATE DIRECTORY ext_tab_dir AS '/path/to/files'; -- 创建外部表目录
CREATE TABLE table_name (
    col1 VARCHAR2(100),
    col2 NUMBER
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        FIELDS TERMINATED BY ','
        REJECT ROWS WITH ALL NULL FIELDS
        (
            col1 CHAR(100),
            col2 CHAR(10)
        )
    )
    LOCATION ('file.csv') -- 文件名
) REJECT LIMIT UNLIMITED;
```

MongoDB:
```javascript
mongoimport --db database_name --collection collection_name --type csv --headerline --file /path/to/file.csv
```