全国省份、城市关联表 mysql（含城市名拼音)

省份表

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!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 TABLE IF NOT EXISTS `province` (
  `id` int(11) NOT NULL auto_increment,
  `prov_id` int(10) unsigned NOT NULL default '0',
  `prov_name` varchar(30) NOT NULL,
  `prov_type` varchar(1) default NULL COMMENT '1 - 直辖市\r\n2 - 行政省\r\n3 - 自治区\r\n4 - 特别行政区\r\n5 - 其他国家\r\n见全局数据字典[省份类型] \r\n',
  `prov_state` varchar(1) default NULL COMMENT '0 - 禁用\r\n1 - 启用',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `province` (`id`, `prov_id`, `prov_name`, `prov_type`, `prov_state`) VALUES
	(1, 1, '国外', '5', '1'),
	(2, 10, '北京', '1', '1'),
	(3, 11, '上海', '1', '1'),
	(4, 12, '天津', '1', '1'),
	(5, 13, '重庆', '1', '1'),
	(6, 14, '河北', '2', '1'),
	(7, 15, '山西', '2', '1'),
	(8, 16, '内蒙古 ', '3', '1'),
	(9, 17, '辽宁', '2', '1'),
	(10, 18, '吉林', '2', '1'),
	(11, 19, '黑龙江', '2', '1'),
	(12, 20, '江苏', '2', '1'),
	(13, 21, '浙江', '2', '1'),
	(14, 22, '安徽', '2', '1'),
	(15, 23, '福建', '2', '1'),
	(16, 24, '江西', '2', '1'),
	(17, 25, '山东', '2', '1'),
	(18, 26, '河南', '2', '1'),
	(19, 27, '湖北', '2', '1'),
	(20, 28, '湖南', '2', '1'),
	(21, 29, '广东', '2', '1'),
	(22, 30, '广西', '3', '1'),
	(23, 31, '海南', '2', '1'),
	(24, 32, '四川', '2', '1'),
	(25, 33, '贵州', '2', '1'),
	(26, 34, '云南', '2', '1'),
	(27, 35, '西藏', '3', '1'),
	(28, 36, '陕西', '2', '1'),
	(29, 37, '甘肃', '2', '1'),
	(30, 38, '青海', '2', '1'),
	(31, 39, '宁夏', '3', '1'),
	(32, 40, '新疆', '3', '1'),
	(33, 41, '香港', '4', '1'),
	(34, 42, '澳门', '4', '1'),
	(35, 43, '台湾', '2', '1');
/*!40000 ALTER TABLE `province` ENABLE KEYS */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_CLIENT