msyql省份、城市及拼音转换

/*
SQLyog Enterprise - MySQL GUI v7.14 
MySQL - 5.5.28 : Database - netinnet_rck
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

CREATE DATABASE /*!32312 IF NOT EXISTS*/`netinnet_rck` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `netinnet_rck`;

/*Table structure for table `rck_city` */

DROP TABLE IF EXISTS `rck_city`;

CREATE TABLE `rck_city` (
  `city_id` char(32) NOT NULL,
  `city_code` varchar(50) NOT NULL,
  `pingyin` varchar(50) DEFAULT NULL,
  `city_name` varchar(100) NOT NULL,
  `province_code` varchar(50) NOT NULL,
  `create_date` varchar(20) NOT NULL,
  `create_user` char(32) NOT NULL,
  PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `rck_city` */

insert  into `rck_city`(`city_id`,`city_code`,`pingyin`,`city_name`,`province_code`,`create_date`,`create_user`) values ('692eb613b78b11e2a8bb14dae9f08d3b','110100','S','市辖区','110000','2013050811111111','1'),('692eb834b78b11e2a8bb14dae9f08d3b','110200','X','县','110000','2013050811111111','1'),('692eb90db78b11e2a8bb14dae9f08d3b','120100','S','市辖区','120000','2013050811111111','1'),('692eb9c6b78b11e2a8bb14dae9f08d3b','120200','X','县','120000','2013050811111111','1'),('692eba79b78b11e2a8bb14dae9f08d3b','130100','S','石家庄市','130000','2013050811111111','1'),('692ebb2eb78b11e2a8bb14dae9f08d3b','130200','T','唐山市','130000','2013050811111111','1'),('692ebbe4b78b11e2a8bb14dae9f08d3b','130300','Q','秦皇岛市','130000','2013050811111111','1'),('692ebc91b78b11e2a8bb14dae9f08d3b','130400','H','邯郸市','130000','2013050811111111','1'),('692ebd47b78b11e2a8bb14dae9f08d3b','130500','X','邢台市','130000','2013050811111111','1'),('692ebdf4b78b11e2a8bb14dae9f08d3b','130600','B','保定市','130000','2013050811111111','1'),('692ebe96b78b11e2a8bb14dae9f08d3b','130700','Z','张家口市','130000','2013050811111111','1'),('692ebf40b78b11e2a8bb14dae9f08d3b','130800','C','承德市','130000','2013050811111111','1'),('692ebff3b78b11e2a8bb14dae9f08d3b','130900','C','沧州市','130000','2013050811111111','1'),('692ec09db78b11e2a8bb14dae9f08d3b','131000','L','廊坊市','130000','2013050811111111','1'),('692ec148b78b11e2a8bb14dae9f08d3b','131100','H','衡水市','130000','2013050811111111','1'),('692ec1f0b78b11e2a8bb14dae9f08d3b','140100','T','太原市','140000','2013050811111111','1'),('692ec2a2b78b11e2a8bb14dae9f08d3b','140200','D','大同市','140000','2013050811111111','1'),('692ec347b78b11e2a8bb14dae9f08d3b','140300','Y','阳泉市','140000','2013050811111111','1'),('692ec3f2b78b11e2a8bb14dae9f08d3b','140400','C','长治市','140000','2013050811111111','1'),('692ec579b78b11e2a8bb14dae9f08d3b','140500','J','晋城市','140000','2013050811111111','1'),('692ec62eb78b11e2a8bb14dae9f08d3b','140600','S','朔州市','140000','2013050811111111','1'),('692ec6d3b78b11e2a8bb14dae9f08d3b','140700','J','晋中市','140000','2013050811111111','1'),('692ec778b78b11e2a8bb14dae9f08d3b','140800','Y','运城市','140000','2013050811111111','1'),('692ec81ab78b11e2a8bb14dae9f08d3b','140900','X','忻州市','140000','2013050811111111','1'),('692ec8c4b78b11e2a8bb14dae9f08d3b','141000','L','临汾市','140000','2013050811111111','1'),('692ec964b78b11e2a8bb14dae9f08d3b','141100','L','吕梁市','140000','2013050811111111','1'),('692eca08b78b11e2a8bb14dae9f08d3b','150100','H','呼和浩特市','150000','2013050811111111','1'),('692ecab0b78b11e2a8bb14dae9f08d3b','150200','B','包头市','150000','2013050811111111','1'),('692ecb5db78b11e2a8bb14dae9f08d3b','150300','W','乌海市','150000','2013050811111111','1'),('692ecbffb78b11e2a8bb14dae9f08d3b','150400','C','赤峰市','150000','2013050811111111','1'),('692ecca7b78b11e2a8bb14dae9f08d3b','150500','T','通辽市','150000','2013050811111111','1'),('692ecd4cb78b11e2a8bb14dae9f08d3b','150600','E','鄂尔多斯市','150000','2013050811111111','1'),('692ecdf6b78b11e2a8bb14dae9f08d3b','150700','H','呼伦贝尔市','150000','2013050811111111','1'),('692ece9eb78b11e2a8bb14dae9f08d3b','150800','B','巴彦淖尔市','150000','2013050811111111','1'),('692ecf48b78b11e2a8bb14dae9f08d3b','150900','W','乌兰察布市','150000','2013050811111111','1'),('692ecff3b78b11e2a8bb14dae9f08d3b','152200','X','兴安盟','150000','2013050811111111','1'),('692ed09db78b11e2a8bb14dae9f08d3b','152500','X','锡林郭勒盟','150000','2013050811111111','1'),('692ed142b78b11e2a8bb14dae9f08d3b','152900','A','阿拉善盟','150000','2013050811111111','1'),('692ed1e9b78b11e2a8bb14dae9f08d3b','210100','S','沈阳市','210000','2013050811111111','1'),('692ed395b78b11e2a8bb14dae9f08d3b','210200','D','大连市','210000','2013050811111111','1'),('692ed442b78b11e2a8bb14dae9f08d3b','210300','A','鞍山市','210000','2013050811111111','1'),('692ed4eab78b11e2a8bb14dae9f08d3b','210400','F','抚顺市','210000','2013050811111111','1'),('692ed58fb78b11e2a8bb14dae9f08d3b','210500','B','本溪市','210000','2013050811111111','1'),('692ed62eb78b11e2a8bb14dae9f08d3b','210600','D','丹东市','210000','2013050811111111','1'),('692ed6d5b78b11e2a8bb14dae9f08d3b','210700','J','锦州市','210000','2013050811111111','1'),('692ed77ab78b11e2a8bb14dae9f08d3b','210800','Y','营口市','210000','2013050811111111','1'),('692ed81cb78b11e2a8bb14dae9f08d3b','210900','F','阜新市','210000','2013050811111111','1'),('692ed8c1b78b11e2a8bb14dae9f08d3b','211000','L','辽阳市','210000','2013050811111111','1'),('692ed966b78b11e2a8bb14dae9f08d3b','211100','P','盘锦市','210000','2013050811111111','1'),('692eda0db78b11e2a8bb14dae9f08d3b','211200','T','铁岭市','210000','2013050811111111','1'),('692edaaab78b11e2a8bb14dae9f08d3b','211300','C','朝阳市','210000','2013050811111111','1'),('692edb4cb78b11e2a8bb14dae9f08d3b','211400','H','葫芦岛市','210000','2013050811111111','1'),('692edbf6b78b11e2a8bb14dae9f08d3b','220100','C','长春市','220000','2013050811111111','1'),('692edc9eb78b11e2a8bb14dae9f08d3b','220200','J','吉林市','220000','2013050811111111','1'),('692edd43b78b11e2a8bb14dae9f08d3b','220300','S','四平市','220000','2013050811111111','1'),('692edde5b78b11e2a8bb14dae9f08d3b','220400','L','辽源市','220000','2013050811111111','1'),('692ede8fb78b11e2a8bb14dae9f08d3b','220500','T','通化市','220000','2013050811111111','1'),('692edf37b78b11e2a8bb14dae9f08d3b','220600','B','白山市','220000','2013050811111111','1'),('692edfd9b78b11e2a8bb14dae9f08d3b','220700','S','松原市','220000','2013050811111111','1'),('692ee07eb78b11e2a8bb14dae9f08d3b','220800','B','白城市','220000','2013050811111111','1'),('692ee285b78b11e2a8bb14dae9f08d3b','222400','Y','延边朝鲜族自治州','220000','2013050811111111','1'),('692ee335b78b11e2a8bb14dae9f08d3b','230100','H','哈尔滨市','230000','2013050811111111','1'),('692ee3dab78b11e2a8bb14dae9f08d3b','230200','Q','齐齐哈尔市','230000','2013050811111111','1'),('692ee482b78b11e2a8bb14dae9f08d3b','230300','J','鸡西市','230000','2013050811111111','1'),('692ee52cb78b11e2a8bb14dae9f08d3b','230400','H','鹤岗市','230000','2013050811111111','1'),('692ee5ceb78b11e2a8bb14dae9f08d3b','230500','S','双鸭山市','230000','2013050811111111','1'),('692ee679b78b11e2a8bb14dae9f08d3b','230600','D','大庆市','230000','2013050811111111','1'),('692ee720b78b11e2a8bb14dae9f08d3b','230700','Y','伊春市','230000','2013050811111111','1'),('692ee7c5b78b11e2a8bb14dae9f08d3b','230800','J','佳木斯市','230000','2013050811111111','1'),('692ee86db78b11e2a8bb14dae9f08d3b','230900','Q','七台河市','230000','2013050811111111','1'),('692ee917b78b11e2a8bb14dae9f08d3b','231000','M','牡丹江市','230000','2013050811111111','1'),('692ee9bfb78b11e2a8bb14dae9f08d3b','231100','H','黑河市','230000','2013050811111111','1'),('692eea66b78b11e2a8bb14dae9f08d3b','231200','S','绥化市','230000','2013050811111111','1'),('692eeb0eb78b11e2a8bb14dae9f08d3b','232700','D','大兴安岭地区','230000','2013050811111111','1'),('692eebb3b78b11e2a8bb14dae9f08d3b','310100','S','市辖区','310000','2013050811111111','1'),('692eec55b78b11e2a8bb14dae9f08d3b','310200','X','县','310000','2013050811111111','1'),('692eecfcb78b11e2a8bb14dae9f08d3b','320100','N','南京市','320000','2013050811111111','1'),('692eeda1b78b11e2a8bb14dae9f08d3b','320200','W','无锡市','320000','2013050811111111','1'),('692eee49b78b11e2a8bb14dae9f08d3b','320300','X','徐州市','320000','2013050811111111','1'),('692eeeebb78b11e2a8bb14dae9f08d3b','320400','C','常州市','320000','2013050811111111','1'),('692eef90b78b11e2a8bb14dae9f08d3b','320500','S','苏州市','320000','2013050811111111','1'),('692ef037b78b11e2a8bb14dae9f08d3b','320600','N','南通市','320000','2013050811111111','1'),('692ef0d9b78b11e2a8bb14dae9f08d3b','320700','L','连云港市','320000','2013050811111111','1'),('692ef32ab78b11e2a8bb14dae9f08d3b','320800','H','淮安市','320000','2013050811111111','1'),('692ef3dcb78b11e2a8bb14dae9f08d3b','320900','Y','盐城市','320000','2013050811111111','1'),('692ef484b78b11e2a8bb14dae9f08d3b','321000','Y','扬州市','320000','2013050811111111','1'),('692ef529b78b11e2a8bb14dae9f08d3b','321100','Z','镇江市','320000','2013050811111111','1'),('692ef5ceb78b11e2a8bb14dae9f08d3b','321200','T','泰州市','320000','2013050811111111','1'),('692ef675b78b11e2a8bb14dae9f08d3b','321300','S','宿迁市','320000','2013050811111111','1'),('692ef717b78b11e2a8bb14dae9f08d3b','330100','H','杭州市','330000','2013050811111111','1'),('692ef7b9b78b11e2a8bb14dae9f08d3b','330200','N','宁波市','330000','2013050811111111','1'),('692ef867b78b11e2a8bb14dae9f08d3b','330300','W','温州市','330000','2013050811111111','1'),('692ef911b78b11e2a8bb14dae9f08d3b','330400','J','嘉兴市','330000','2013050811111111','1'),('692ef9b9b78b11e2a8bb14dae9f08d3b','330500','H','湖州市','330000','2013050811111111','1'),('692efa60b78b11e2a8bb14dae9f08d3b','330600','S','绍兴市','330000','2013050811111111','1'),('692efb08b78b11e2a8bb14dae9f08d3b','330700','J','金华市','330000','2013050811111111','1'),('692efba7b78b11e2a8bb14dae9f08d3b','330800','Z','衢州市','330000','2013050811111111','1'),('692efc49b78b11e2a8bb14dae9f08d3b','330900','Z','舟山市','330000','2013050811111111','1'),('692efcf1b78b11e2a8bb14dae9f08d3b','331000','T','台州市','330000','2013050811111111','1'),('692efd93b78b11e2a8bb14dae9f08d3b','331100','L','丽水市','330000','2013050811111111','1'),('692efe3ab78b11e2a8bb14dae9f08d3b','340100','H','合肥市','340000','2013050811111111','1'),('692efedcb78b11e2a8bb14dae9f08d3b','340200','W','芜湖市','340000','2013050811111111','1'),('692eff81b78b11e2a8bb14dae9f08d3b','340300','B','蚌埠市','340000','2013050811111111','1'),('692f0023b78b11e2a8bb14dae9f08d3b','340400','H','淮南市','340000','2013050811111111','1'),('692f00ceb78b11e2a8bb14dae9f08d3b','340500','M','马鞍山市','340000','2013050811111111','1'),('692f0173b78b11e2a8bb14dae9f08d3b','340600','H','淮北市','340000','2013050811111111','1'),('692f021ab78b11e2a8bb14dae9f08d3b','340700','T','铜陵市','340000','2013050811111111','1'),('692f04e5b78b11e2a8bb14dae9f08d3b','340800','A','安庆市','340000','2013050811111111','1'),('692f0593b78b11e2a8bb14dae9f08d3b','341000','H','黄山市','340000','2013050811111111','1'),('692f0635b78b11e2a8bb14dae9f08d3b','341100','C','滁州市','340000','2013050811111111','1'),('692f06d9b78b11e2a8bb14dae9f08d3b','341200','F','阜阳市','340000','2013050811111111','1'),('692f077eb78b11e2a8bb14dae9f08d3b','341300','S','宿州市','340000','2013050811111111','1'),('692f0826b78b11e2a8bb14dae9f08d3b','341400','C','巢湖市','340000','2013050811111111','1'),('692f08c8b78b11e2a8bb14dae9f08d3b','341500','L','六安市','340000','2013050811111111','1'),('692f0967b78b11e2a8bb14dae9f08d3b','341600','Z','亳州市','340000','2013050811111111','1'),('692f0a09b78b11e2a8bb14dae9f08d3b','341700','C','池州市','340000','2013050811111111','1'),('692f0ab1b78b11e2a8bb14dae9f08d3b','341800','X','宣城市','340000','2013050811111111','1'),('692f0b56b78b11e2a8bb14dae9f08d3b','350100','F','福州市','350000','2013050811111111','1'),('692f0bf8b78b11e2a8bb14dae9f08d3b','350200','X','厦门市','350000','2013050811111111','1'),('692f0c97b78b11e2a8bb14dae9f08d3b','350300','P','莆田市','350000','2013050811111111','1'),('692f0d44b78b11e2a8bb14dae9f08d3b','350400','S','三明市','350000','2013050811111111','1'),('692f0de6b78b11e2a8bb14dae9f08d3b','350500','Q','泉州市','350000','2013050811111111','1'),('692f0e88b78b11e2a8bb14dae9f08d3b','350600','Z','漳州市','350000','2013050811111111','1'),('692f0f2ab78b11e2a8bb14dae9f08d3b','350700','N','南平市','350000','2013050811111111','1'),('692f0fd2b78b11e2a8bb14dae9f08d3b','350800','L','龙岩市','350000','2013050811111111','1'),('692f1077b78b11e2a8bb14dae9f08d3b','350900','N','宁德市','350000','2013050811111111','1'),('692f1119b78b11e2a8bb14dae9f08d3b','360100','N','南昌市','360000','2013050811111111','1'),('692f11bbb78b11e2a8bb14dae9f08d3b','360200','J','景德镇市','360000','2013050811111111','1'),('692f1265b78b11e2a8bb14dae9f08d3b','360300','P','萍乡市','360000','2013050811111111','1'),('692f130ab78b11e2a8bb14dae9f08d3b','360400','J','九江市','360000','2013050811111111','1'),('692f13a9b78b11e2a8bb14dae9f08d3b','360500','X','新余市','360000','2013050811111111','1'),('692f1454b78b11e2a8bb14dae9f08d3b','360600','Y','鹰潭市','360000','2013050811111111','1'),('692f14fbb78b11e2a8bb14dae9f08d3b','360700','G','赣州市','360000','2013050811111111','1'),('692f1825b78b11e2a8bb14dae9f08d3b','360800','J','吉安市','360000','2013050811111111','1'),('692f18cdb78b11e2a8bb14dae9f08d3b','360900','Y','宜春市','360000','2013050811111111','1'),('692f1977b78b11e2a8bb14dae9f08d3b','361000','F','抚州市','360000','2013050811111111','1'),('692f1a1fb78b11e2a8bb14dae9f08d3b','361100','S','上饶市','360000','2013050811111111','1'),('692f1ac7b78b11e2a8bb14dae9f08d3b','370100','J','济南市','370000','2013050811111111','1'),('692f1b6bb78b11e2a8bb14dae9f08d3b','370200','Q','青岛市','370000','2013050811111111','1'),('692f1c2cb78b11e2a8bb14dae9f08d3b','370300','Z','淄博市','370000','2013050811111111','1'),('692f1cd9b78b11e2a8bb14dae9f08d3b','370400','Z','枣庄市','370000','2013050811111111','1'),('692f1d79b78b11e2a8bb14dae9f08d3b','370500','D','东营市','370000','2013050811111111','1'),('692f1e20b78b11e2a8bb14dae9f08d3b','370600','Y','烟台市','370000','2013050811111111','1'),('692f1ecbb78b11e2a8bb14dae9f08d3b','370700','W','潍坊市','370000','2013050811111111','1'),('692f1f72b78b11e2a8bb14dae9f08d3b','370800','J','济宁市','370000','2013050811111111','1'),('692f2022b78b11e2a8bb14dae9f08d3b','370900','T','泰安市','370000','2013050811111111','1'),('692f20cab78b11e2a8bb14dae9f08d3b','371000','W','威海市','370000','2013050811111111','1'),('692f2172b78b11e2a8bb14dae9f08d3b','371100','R','日照市','370000','2013050811111111','1'),('692f2219b78b11e2a8bb14dae9f08d3b','371200','L','莱芜市','370000','2013050811111111','1'),('692f22beb78b11e2a8bb14dae9f08d3b','371300','L','临沂市','370000','2013050811111111','1'),('692f2368b78b11e2a8bb14dae9f08d3b','371400','D','德州市','370000','2013050811111111','1'),('692f240ab78b11e2a8bb14dae9f08d3b','371500','L','聊城市','370000','2013050811111111','1'),('692f24b2b78b11e2a8bb14dae9f08d3b','371600','B','滨州市','370000','2013050811111111','1'),('692f2557b78b11e2a8bb14dae9f08d3b','371700','H','荷泽市','370000','2013050811111111','1'),('692f2601b78b11e2a8bb14dae9f08d3b','410100','Z','郑州市','410000','2013050811111111','1'),('692f26a3b78b11e2a8bb14dae9f08d3b','410200','K','开封市','410000','2013050811111111','1'),('692f274bb78b11e2a8bb14dae9f08d3b','410300','L','洛阳市','410000','2013050811111111','1'),('692f27f3b78b11e2a8bb14dae9f08d3b','410400','P','平顶山市','410000','2013050811111111','1'),('692f2897b78b11e2a8bb14dae9f08d3b','410500','A','安阳市','410000','2013050811111111','1'),('692f2939b78b11e2a8bb14dae9f08d3b','410600','H','鹤壁市','410000','2013050811111111','1'),('692f29deb78b11e2a8bb14dae9f08d3b','410700','X','新乡市','410000','2013050811111111','1'),('692f2d91b78b11e2a8bb14dae9f08d3b','410800','J','焦作市','410000','2013050811111111','1'),('692f2e3cb78b11e2a8bb14dae9f08d3b','410900','Z','濮阳市','410000','2013050811111111','1'),('692f2edbb78b11e2a8bb14dae9f08d3b','411000','X','许昌市','410000','2013050811111111','1'),('692f2f88b78b11e2a8bb14dae9f08d3b','411100','Z','漯河市','410000','2013050811111111','1'),('692f3030b78b11e2a8bb14dae9f08d3b','411200','S','三门峡市','410000','2013050811111111','1'),('692f30d5b78b11e2a8bb14dae9f08d3b','411300','N','南阳市','410000','2013050811111111','1'),('692f3177b78b11e2a8bb14dae9f08d3b','411400','S','商丘市','410000','2013050811111111','1'),('692f3224b78b11e2a8bb14dae9f08d3b','411500','X','信阳市','410000','2013050811111111','1'),('692f32c9b78b11e2a8bb14dae9f08d3b','411600','Z','周口市','410000','2013050811111111','1'),('692f336db78b11e2a8bb14dae9f08d3b','411700','Z','驻马店市','410000','2013050811111111','1'),('692f3418b78b11e2a8bb14dae9f08d3b','420100','W','武汉市','420000','2013050811111111','1'),('692f34c2b78b11e2a8bb14dae9f08d3b','420200','H','黄石市','420000','2013050811111111','1'),('692f3562b78b11e2a8bb14dae9f08d3b','420300','S','十堰市','420000','2013050811111111','1'),('692f3606b78b11e2a8bb14dae9f08d3b','420500','Y','宜昌市','420000','2013050811111111','1'),('692f36abb78b11e2a8bb14dae9f08d3b','420600','X','襄樊市','420000','2013050811111111','1'),('692f3750b78b11e2a8bb14dae9f08d3b','420700','E','鄂州市','420000','2013050811111111','1'),('692f37f2b78b11e2a8bb14dae9f08d3b','420800','J','荆门市','420000','2013050811111111','1'),('692f3891b78b11e2a8bb14dae9f08d3b','420900','X','孝感市','420000','2013050811111111','1'),('692f3933b78b11e2a8bb14dae9f08d3b','421000','J','荆州市','420000','2013050811111111','1'),('692f39d8b78b11e2a8bb14dae9f08d3b','421100','H','黄冈市','420000','2013050811111111','1'),('692f3a7db78b11e2a8bb14dae9f08d3b','421200','X','咸宁市','420000','2013050811111111','1'),('692f3b25b78b11e2a8bb14dae9f08d3b','421300','S','随州市','420000','2013050811111111','1'),('692f3bc1b78b11e2a8bb14dae9f08d3b','422800','E','恩施土家族苗族自治州','420000','2013050811111111','1'),('692f3c6bb78b11e2a8bb14dae9f08d3b','429000','S','省直辖行政单位','420000','2013050811111111','1'),('692f3d10b78b11e2a8bb14dae9f08d3b','430100','C','长沙市','430000','2013050811111111','1'),('692f3db5b78b11e2a8bb14dae9f08d3b','430200','Z','株洲市','430000','2013050811111111','1'),('692f3e5ab78b11e2a8bb14dae9f08d3b','430300','X','湘潭市','430000','2013050811111111','1'),('692f3f02b78b11e2a8bb14dae9f08d3b','430400','H','衡阳市','430000','2013050811111111','1'),('692f3fa4b78b11e2a8bb14dae9f08d3b','430500','S','邵阳市','430000','2013050811111111','1'),('692f43c1b78b11e2a8bb14dae9f08d3b','430600','Y','岳阳市','430000','2013050811111111','1'),('692f446bb78b11e2a8bb14dae9f08d3b','430700','C','常德市','430000','2013050811111111','1'),('692f4516b78b11e2a8bb14dae9f08d3b','430800','Z','张家界市','430000','2013050811111111','1'),('692f45bdb78b11e2a8bb14dae9f08d3b','430900','Y','益阳市','430000','2013050811111111','1'),('692f4668b78b11e2a8bb14dae9f08d3b','431000','C','郴州市','430000','2013050811111111','1'),('692f470ab78b11e2a8bb14dae9f08d3b','431100','Y','永州市','430000','2013050811111111','1'),('692f47b4b78b11e2a8bb14dae9f08d3b','431200','H','怀化市','430000','2013050811111111','1'),('692f4856b78b11e2a8bb14dae9f08d3b','431300','L','娄底市','430000','2013050811111111','1'),('692f5728b78b11e2a8bb14dae9f08d3b','433100','X','湘西土家族苗族自治州','430000','2013050811111111','1'),('692f5810b78b11e2a8bb14dae9f08d3b','440100','G','广州市','440000','2013050811111111','1'),('692f58d6b78b11e2a8bb14dae9f08d3b','440200','S','韶关市','440000','2013050811111111','1'),('692f599fb78b11e2a8bb14dae9f08d3b','440300','S','深圳市','440000','2013050811111111','1'),('692f5a74b78b11e2a8bb14dae9f08d3b','440400','Z','珠海市','440000','2013050811111111','1'),('692f5b1eb78b11e2a8bb14dae9f08d3b','440500','S','汕头市','440000','2013050811111111','1'),('692f5bceb78b11e2a8bb14dae9f08d3b','440600','F','佛山市','440000','2013050811111111','1'),('692f5c81b78b11e2a8bb14dae9f08d3b','440700','J','江门市','440000','2013050811111111','1'),('692f5d31b78b11e2a8bb14dae9f08d3b','440800','Z','湛江市','440000','2013050811111111','1'),('692f5ddeb78b11e2a8bb14dae9f08d3b','440900','M','茂名市','440000','2013050811111111','1'),('692f5e88b78b11e2a8bb14dae9f08d3b','441200','Z','肇庆市','440000','2013050811111111','1'),('692f5f36b78b11e2a8bb14dae9f08d3b','441300','H','惠州市','440000','2013050811111111','1'),('692f5fe3b78b11e2a8bb14dae9f08d3b','441400','M','梅州市','440000','2013050811111111','1'),('692f608db78b11e2a8bb14dae9f08d3b','441500','S','汕尾市','440000','2013050811111111','1'),('692f613db78b11e2a8bb14dae9f08d3b','441600','H','河源市','440000','2013050811111111','1'),('692f61e8b78b11e2a8bb14dae9f08d3b','441700','Y','阳江市','440000','2013050811111111','1'),('692f6298b78b11e2a8bb14dae9f08d3b','441800','Q','清远市','440000','2013050811111111','1'),('692f6342b78b11e2a8bb14dae9f08d3b','441900','D','东莞市','440000','2013050811111111','1'),('692f63ecb78b11e2a8bb14dae9f08d3b','442000','Z','中山市','440000','2013050811111111','1'),('692f649ab78b11e2a8bb14dae9f08d3b','445100','C','潮州市','440000','2013050811111111','1'),('692f6547b78b11e2a8bb14dae9f08d3b','445200','J','揭阳市','440000','2013050811111111','1'),('692f65f1b78b11e2a8bb14dae9f08d3b','445300','Y','云浮市','440000','2013050811111111','1'),('692f66a1b78b11e2a8bb14dae9f08d3b','450100','N','南宁市','450000','2013050811111111','1'),('692f6e10b78b11e2a8bb14dae9f08d3b','450200','L','柳州市','450000','2013050811111111','1'),('692f6ec5b78b11e2a8bb14dae9f08d3b','450300','G','桂林市','450000','2013050811111111','1'),('692f6f73b78b11e2a8bb14dae9f08d3b','450400','W','梧州市','450000','2013050811111111','1'),('692f7020b78b11e2a8bb14dae9f08d3b','450500','B','北海市','450000','2013050811111111','1'),('692f70cab78b11e2a8bb14dae9f08d3b','450600','F','防城港市','450000','2013050811111111','1'),('692f717db78b11e2a8bb14dae9f08d3b','450700','Q','钦州市','450000','2013050811111111','1'),('692f722db78b11e2a8bb14dae9f08d3b','450800','G','贵港市','450000','2013050811111111','1'),('692f72d7b78b11e2a8bb14dae9f08d3b','450900','Y','玉林市','450000','2013050811111111','1'),('692f7382b78b11e2a8bb14dae9f08d3b','451000','B','百色市','450000','2013050811111111','1'),('692f742fb78b11e2a8bb14dae9f08d3b','451100','H','贺州市','450000','2013050811111111','1'),('692f74dfb78b11e2a8bb14dae9f08d3b','451200','H','河池市','450000','2013050811111111','1'),('692f758cb78b11e2a8bb14dae9f08d3b','451300','L','来宾市','450000','2013050811111111','1'),('692f7637b78b11e2a8bb14dae9f08d3b','451400','C','崇左市','450000','2013050811111111','1'),('692f76e4b78b11e2a8bb14dae9f08d3b','460100','H','海口市','460000','2013050811111111','1'),('692f7794b78b11e2a8bb14dae9f08d3b','460200','S','三亚市','460000','2013050811111111','1'),('692f783eb78b11e2a8bb14dae9f08d3b','469000','S','省直辖县级行政单位','460000','2013050811111111','1'),('692f78eeb78b11e2a8bb14dae9f08d3b','500100','S','市辖区','500000','2013050811111111','1'),('692f79a4b78b11e2a8bb14dae9f08d3b','500200','X','县','500000','2013050811111111','1'),('692f7a54b78b11e2a8bb14dae9f08d3b','500300','S','市','500000','2013050811111111','1'),('692f7afeb78b11e2a8bb14dae9f08d3b','510100','C','成都市','510000','2013050811111111','1'),('692f7ba9b78b11e2a8bb14dae9f08d3b','510300','Z','自贡市','510000','2013050811111111','1'),('692f7c59b78b11e2a8bb14dae9f08d3b','510400','P','攀枝花市','510000','2013050811111111','1'),('692f7d00b78b11e2a8bb14dae9f08d3b','510500','Z','泸州市','510000','2013050811111111','1'),('692f7dabb78b11e2a8bb14dae9f08d3b','510600','D','德阳市','510000','2013050811111111','1'),('692f7e5bb78b11e2a8bb14dae9f08d3b','510700','M','绵阳市','510000','2013050811111111','1'),('692f7f0bb78b11e2a8bb14dae9f08d3b','510800','G','广元市','510000','2013050811111111','1'),('692f7fb5b78b11e2a8bb14dae9f08d3b','510900','S','遂宁市','510000','2013050811111111','1'),('692f8062b78b11e2a8bb14dae9f08d3b','511000','N','内江市','510000','2013050811111111','1'),('692f8110b78b11e2a8bb14dae9f08d3b','511100','L','乐山市','510000','2013050811111111','1'),('692f81c0b78b11e2a8bb14dae9f08d3b','511300','N','南充市','510000','2013050811111111','1'),('692f8267b78b11e2a8bb14dae9f08d3b','511400','M','眉山市','510000','2013050811111111','1'),('692f8314b78b11e2a8bb14dae9f08d3b','511500','Y','宜宾市','510000','2013050811111111','1'),('692f83bfb78b11e2a8bb14dae9f08d3b','511600','G','广安市','510000','2013050811111111','1'),('692f846fb78b11e2a8bb14dae9f08d3b','511700','D','达州市','510000','2013050811111111','1'),('692f851cb78b11e2a8bb14dae9f08d3b','511800','Y','雅安市','510000','2013050811111111','1'),('692f8e41b78b11e2a8bb14dae9f08d3b','511900','B','巴中市','510000','2013050811111111','1'),('692f8ef1b78b11e2a8bb14dae9f08d3b','512000','Z','资阳市','510000','2013050811111111','1'),('692f8fa4b78b11e2a8bb14dae9f08d3b','513200','A','阿坝藏族羌族自治州','510000','2013050811111111','1'),('692f9051b78b11e2a8bb14dae9f08d3b','513300','G','甘孜藏族自治州','510000','2013050811111111','1'),('692f9104b78b11e2a8bb14dae9f08d3b','513400','L','凉山彝族自治州','510000','2013050811111111','1'),('692f91b1b78b11e2a8bb14dae9f08d3b','520100','G','贵阳市','520000','2013050811111111','1'),('692f9267b78b11e2a8bb14dae9f08d3b','520200','L','六盘水市','520000','2013050811111111','1'),('692f9311b78b11e2a8bb14dae9f08d3b','520300','Z','遵义市','520000','2013050811111111','1'),('692f93beb78b11e2a8bb14dae9f08d3b','520400','A','安顺市','520000','2013050811111111','1'),('692f9469b78b11e2a8bb14dae9f08d3b','522200','T','铜仁地区','520000','2013050811111111','1'),('692f9521b78b11e2a8bb14dae9f08d3b','522300','Q','黔西南布依族苗族自治州','520000','2013050811111111','1'),('692f95ceb78b11e2a8bb14dae9f08d3b','522400','B','毕节地区','520000','2013050811111111','1'),('692f9679b78b11e2a8bb14dae9f08d3b','522600','Q','黔东南苗族侗族自治州','520000','2013050811111111','1'),('692f9726b78b11e2a8bb14dae9f08d3b','522700','Q','黔南布依族苗族自治州','520000','2013050811111111','1'),('692f97d6b78b11e2a8bb14dae9f08d3b','530100','K','昆明市','530000','2013050811111111','1'),('692f9883b78b11e2a8bb14dae9f08d3b','530300','Q','曲靖市','530000','2013050811111111','1'),('692f9933b78b11e2a8bb14dae9f08d3b','530400','Y','玉溪市','530000','2013050811111111','1'),('692f99ddb78b11e2a8bb14dae9f08d3b','530500','B','保山市','530000','2013050811111111','1'),('692f9a8db78b11e2a8bb14dae9f08d3b','530600','Z','昭通市','530000','2013050811111111','1'),('692f9b38b78b11e2a8bb14dae9f08d3b','530700','L','丽江市','530000','2013050811111111','1'),('692f9be0b78b11e2a8bb14dae9f08d3b','530800','S','思茅市','530000','2013050811111111','1'),('692f9c8ab78b11e2a8bb14dae9f08d3b','530900','L','临沧市','530000','2013050811111111','1'),('692f9d37b78b11e2a8bb14dae9f08d3b','532300','C','楚雄彝族自治州','530000','2013050811111111','1'),('692f9de2b78b11e2a8bb14dae9f08d3b','532500','H','红河哈尼族彝族自治州','530000','2013050811111111','1'),('692f9e94b78b11e2a8bb14dae9f08d3b','532600','W','文山壮族苗族自治州','530000','2013050811111111','1'),('692f9f39b78b11e2a8bb14dae9f08d3b','532800','X','西双版纳傣族自治州','530000','2013050811111111','1'),('692f9fe9b78b11e2a8bb14dae9f08d3b','532900','D','大理白族自治州','530000','2013050811111111','1'),('692fa099b78b11e2a8bb14dae9f08d3b','533100','D','德宏傣族景颇族自治州','530000','2013050811111111','1'),('692fa141b78b11e2a8bb14dae9f08d3b','533300','N','怒江傈僳族自治州','530000','2013050811111111','1'),('692fa1f1b78b11e2a8bb14dae9f08d3b','533400','D','迪庆藏族自治州','530000','2013050811111111','1'),('692fa2a4b78b11e2a8bb14dae9f08d3b','540100','L','拉萨市','540000','2013050811111111','1'),('692fa351b78b11e2a8bb14dae9f08d3b','542100','C','昌都地区','540000','2013050811111111','1'),('692fa3f8b78b11e2a8bb14dae9f08d3b','542200','S','山南地区','540000','2013050811111111','1'),('692fa4abb78b11e2a8bb14dae9f08d3b','542300','R','日喀则地区','540000','2013050811111111','1'),('692fa558b78b11e2a8bb14dae9f08d3b','542400','N','那曲地区','540000','2013050811111111','1'),('692fa603b78b11e2a8bb14dae9f08d3b','542500','A','阿里地区','540000','2013050811111111','1'),('692fa6b0b78b11e2a8bb14dae9f08d3b','542600','L','林芝地区','540000','2013050811111111','1'),('692fa8c3b78b11e2a8bb14dae9f08d3b','610100','X','西安市','610000','2013050811111111','1'),('692fa984b78b11e2a8bb14dae9f08d3b','610200','T','铜川市','610000','2013050811111111','1'),('692faa34b78b11e2a8bb14dae9f08d3b','610300','B','宝鸡市','610000','2013050811111111','1'),('692fb523b78b11e2a8bb14dae9f08d3b','610400','X','咸阳市','610000','2013050811111111','1'),('692fb5deb78b11e2a8bb14dae9f08d3b','610500','W','渭南市','610000','2013050811111111','1'),('692fb691b78b11e2a8bb14dae9f08d3b','610600','Y','延安市','610000','2013050811111111','1'),('692fb74cb78b11e2a8bb14dae9f08d3b','610700','H','汉中市','610000','2013050811111111','1'),('692fb802b78b11e2a8bb14dae9f08d3b','610800','Y','榆林市','610000','2013050811111111','1'),('692fb8bdb78b11e2a8bb14dae9f08d3b','610900','A','安康市','610000','2013050811111111','1'),('692fb972b78b11e2a8bb14dae9f08d3b','611000','S','商洛市','610000','2013050811111111','1'),('692fba22b78b11e2a8bb14dae9f08d3b','620100','L','兰州市','620000','2013050811111111','1'),('692fbad2b78b11e2a8bb14dae9f08d3b','620200','J','嘉峪关市','620000','2013050811111111','1'),('692fbb85b78b11e2a8bb14dae9f08d3b','620300','J','金昌市','620000','2013050811111111','1'),('692fbc35b78b11e2a8bb14dae9f08d3b','620400','B','白银市','620000','2013050811111111','1'),('692fbce5b78b11e2a8bb14dae9f08d3b','620500','T','天水市','620000','2013050811111111','1'),('692fbd98b78b11e2a8bb14dae9f08d3b','620600','W','武威市','620000','2013050811111111','1'),('692fbe4bb78b11e2a8bb14dae9f08d3b','620700','Z','张掖市','620000','2013050811111111','1'),('692fbef8b78b11e2a8bb14dae9f08d3b','620800','P','平凉市','620000','2013050811111111','1'),('692fbfa5b78b11e2a8bb14dae9f08d3b','620900','J','酒泉市','620000','2013050811111111','1'),('692fc058b78b11e2a8bb14dae9f08d3b','621000','Q','庆阳市','620000','2013050811111111','1'),('692fc10bb78b11e2a8bb14dae9f08d3b','621100','D','定西市','620000','2013050811111111','1'),('692fc1b8b78b11e2a8bb14dae9f08d3b','621200','L','陇南市','620000','2013050811111111','1'),('692fc268b78b11e2a8bb14dae9f08d3b','622900','L','临夏回族自治州','620000','2013050811111111','1'),('692fc320b78b11e2a8bb14dae9f08d3b','623000','G','甘南藏族自治州','620000','2013050811111111','1'),('692fc3d0b78b11e2a8bb14dae9f08d3b','630100','X','西宁市','630000','2013050811111111','1'),('692fc47db78b11e2a8bb14dae9f08d3b','632100','H','海东地区','630000','2013050811111111','1'),('692fc530b78b11e2a8bb14dae9f08d3b','632200','H','海北藏族自治州','630000','2013050811111111','1'),('692fc5e3b78b11e2a8bb14dae9f08d3b','632300','H','黄南藏族自治州','630000','2013050811111111','1'),('692fc693b78b11e2a8bb14dae9f08d3b','632500','H','海南藏族自治州','630000','2013050811111111','1'),('692fc743b78b11e2a8bb14dae9f08d3b','632600','G','果洛藏族自治州','630000','2013050811111111','1'),('692fc7f3b78b11e2a8bb14dae9f08d3b','632700','Y','玉树藏族自治州','630000','2013050811111111','1'),('692fc8a9b78b11e2a8bb14dae9f08d3b','632800','H','海西蒙古族藏族自治州','630000','2013050811111111','1'),('692fc959b78b11e2a8bb14dae9f08d3b','640100','Y','银川市','640000','2013050811111111','1'),('692fca09b78b11e2a8bb14dae9f08d3b','640200','S','石嘴山市','640000','2013050811111111','1'),('692fcabbb78b11e2a8bb14dae9f08d3b','640300','W','吴忠市','640000','2013050811111111','1'),('692fcb71b78b11e2a8bb14dae9f08d3b','640400','G','固原市','640000','2013050811111111','1'),('692fcc1eb78b11e2a8bb14dae9f08d3b','640500','Z','中卫市','640000','2013050811111111','1'),('692fccceb78b11e2a8bb14dae9f08d3b','650100','W','乌鲁木齐市','650000','2013050811111111','1'),('692fcd7eb78b11e2a8bb14dae9f08d3b','650200','K','克拉玛依市','650000','2013050811111111','1'),('692fce31b78b11e2a8bb14dae9f08d3b','652100','T','吐鲁番地区','650000','2013050811111111','1'),('692fcee4b78b11e2a8bb14dae9f08d3b','652200','H','哈密地区','650000','2013050811111111','1'),('692fcf94b78b11e2a8bb14dae9f08d3b','652300','C','昌吉回族自治州','650000','2013050811111111','1'),('692fd044b78b11e2a8bb14dae9f08d3b','652700','B','博尔塔拉蒙古自治州','650000','2013050811111111','1'),('692fd0fcb78b11e2a8bb14dae9f08d3b','652800','B','巴音郭楞蒙古自治州','650000','2013050811111111','1'),('692fd1acb78b11e2a8bb14dae9f08d3b','652900','A','阿克苏地区','650000','2013050811111111','1'),('692fd25fb78b11e2a8bb14dae9f08d3b','653000','K','克孜勒苏柯尔克孜自治州','650000','2013050811111111','1'),('692fd312b78b11e2a8bb14dae9f08d3b','653100','K','喀什地区','650000','2013050811111111','1'),('692fd3c7b78b11e2a8bb14dae9f08d3b','653200','H','和田地区','650000','2013050811111111','1'),('692fd475b78b11e2a8bb14dae9f08d3b','654000','Y','伊犁哈萨克自治州','650000','2013050811111111','1'),('692fd52ab78b11e2a8bb14dae9f08d3b','654200','T','塔城地区','650000','2013050811111111','1'),('692fd5d7b78b11e2a8bb14dae9f08d3b','654300','A','阿勒泰地区','650000','2013050811111111','1'),('692fddb8b78b11e2a8bb14dae9f08d3b','659000','S','省直辖行政单位','650000','2013050811111111','1');

/*Table structure for table `rck_province` */

DROP TABLE IF EXISTS `rck_province`;

CREATE TABLE `rck_province` (
  `province_id` char(32) NOT NULL,
  `province_code` varchar(50) NOT NULL,
  `pingyin` varchar(50) DEFAULT NULL,
  `province_name` varchar(100) NOT NULL,
  `create_date` varchar(20) NOT NULL,
  `create_user` char(32) NOT NULL,
  PRIMARY KEY (`province_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `rck_province` */

insert  into `rck_province`(`province_id`,`province_code`,`pingyin`,`province_name`,`create_date`,`create_user`) values ('1eaccb0db78a11e2a8bb14dae9f08d3b','110000','B','北京市','2013050811111111','1'),('1eb6f900b78a11e2a8bb14dae9f08d3b','120000','T','天津市','2013050811111111','1'),('1eb95ac5b78a11e2a8bb14dae9f08d3b','130000','H','河北省','2013050811111111','1'),('1ebc01e9b78a11e2a8bb14dae9f08d3b','140000','S','山西省','2013050811111111','1'),('1ebe89e0b78a11e2a8bb14dae9f08d3b','150000','N','内蒙古自治区','2013050811111111','1'),('1ec11530b78a11e2a8bb14dae9f08d3b','210000','L','辽宁省','2013050811111111','1'),('1ec37560b78a11e2a8bb14dae9f08d3b','220000','J','吉林省','2013050811111111','1'),('1ec629a8b78a11e2a8bb14dae9f08d3b','230000','H','黑龙江省','2013050811111111','1'),('1ec89cdab78a11e2a8bb14dae9f08d3b','310000','S','上海市','2013050811111111','1'),('1ecb4094b78a11e2a8bb14dae9f08d3b','320000','J','江苏省','2013050811111111','1'),('1ecf1bd9b78a11e2a8bb14dae9f08d3b','330000','Z','浙江省','2013050811111111','1'),('1ed19d28b78a11e2a8bb14dae9f08d3b','340000','A','安徽省','2013050811111111','1'),('1ed410ffb78a11e2a8bb14dae9f08d3b','350000','F','福建省','2013050811111111','1'),('1ed6b781b78a11e2a8bb14dae9f08d3b','360000','J','江西省','2013050811111111','1'),('1ed91b69b78a11e2a8bb14dae9f08d3b','370000','S','山东省','2013050811111111','1'),('1edbcc44b78a11e2a8bb14dae9f08d3b','410000','H','河南省','2013050811111111','1'),('1edfb5cdb78a11e2a8bb14dae9f08d3b','420000','H','湖北省','2013050811111111','1'),('1ee22e95b78a11e2a8bb14dae9f08d3b','430000','H','湖南省','2013050811111111','1'),('1ee49e47b78a11e2a8bb14dae9f08d3b','440000','G','广东省','2013050811111111','1'),('1ee73c9ab78a11e2a8bb14dae9f08d3b','450000','G','广西壮族自治区','2013050811111111','1'),('1ee9a3a1b78a11e2a8bb14dae9f08d3b','460000','H','海南省','2013050811111111','1'),('1eec4954b78a11e2a8bb14dae9f08d3b','500000','Z','重庆市','2013050811111111','1'),('1ef0282eb78a11e2a8bb14dae9f08d3b','510000','S','四川省','2013050811111111','1'),('1ef2ae0fb78a11e2a8bb14dae9f08d3b','520000','G','贵州省','2013050811111111','1'),('1ef511f2b78a11e2a8bb14dae9f08d3b','530000','Y','云南省','2013050811111111','1'),('1ef812e1b78a11e2a8bb14dae9f08d3b','620000','G','甘肃省','2013050811111111','1'),('1efa2b42b78a11e2a8bb14dae9f08d3b','630000','Q','青海省','2013050811111111','1'),('1efce5e4b78a11e2a8bb14dae9f08d3b','640000','N','宁夏回族自治区','2013050811111111','1'),('1f00a621b78a11e2a8bb14dae9f08d3b','650000','X','新疆维吾尔自治区','2013050811111111','1'),('1f03385db78a11e2a8bb14dae9f08d3b','710000','T','台湾省','2013050811111111','1'),('1f05a524b78a11e2a8bb14dae9f08d3b','810000','X','香港特别行政区','2013050811111111','1'),('1f1021f9b78a11e2a8bb14dae9f08d3b','820000','A','澳门特别行政区','2013050811111111','1');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

Select city_id,city_name, 
ELT(INTERVAL(CONV(HEX(left(CONVERT(city_name USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,
0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P',
'Q','R','S','T','W','X','Y','Z') as PY 

FROM rck_province

update rck_province set pingyin=ELT(INTERVAL(CONV(HEX(left(CONVERT(province_name USING gbk),1)),16,10),
0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,
0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,
0xCBFA,0xCDDA,0xCEF4,0xD1B9,0xD4D1),
'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P',
'Q','R','S','T','W','X','Y','Z')