XML文件空格符问题

XML文件

<?xml version="1.0" encoding="utf-8"?><Location>  <CountryRegion Name="Afghanistan" Code="AFG">    <State >      <City Name="Herat" Code="HEA"/>      <City Name="Kabul" Code="KBL"/>      <City Name="Kandahar" Code="KDH"/>      <City Name="Mazar-i Sharif" Code="MZR"/>    </State>  </CountryRegion>  <CountryRegion Name="Aland lslands" Code="ALA" /><CountryRegion Name="United States" Code="USA">    <State Name="Alabama" Code="AL"></CountryRegion>

我把上述xml的信息转存到mysql，在查询的时候发现中间带有空格键的某些值是可以查询的，而某些却查询不到.
比如
SELECT * FROM `dux_region` WHERE `region_name` = 'Aland lslands' 可以查询出来
而手写的'United States'
SELECT * FROM `dux_region` WHERE `region_name` = 'United States' 不能查出数据
但是从XML中复制的 ‘United States’却可以
网上说是中间 空格符 的问题
这个问题如何解决

回复讨论(解决方案)

可能存在 2 字节的 utf-8 空白字符
贴出经 base64 编码的原始 XML 的内容

文件太大了，我贴出一部分吧。

<?xml version="1.0" encoding="utf-8"?><Location>  <CountryRegion Name="Afghanistan" Code="AFG">    <State >      <City Name="Herat" Code="HEA"/>      <City Name="Kabul" Code="KBL"/>      <City Name="Kandahar" Code="KDH"/>      <City Name="Mazar-i Sharif" Code="MZR"/>    </State>  </CountryRegion>  <CountryRegion Name="Aland lslands" Code="ALA" />  <CountryRegion Name="Albania" Code="ALB">    <State >      <City Name="Berat" Code="BR"/>      <City Name="Diber" Code="DI"/>      <City Name="Durres" Code="DR"/>      <City Name="Elbasan" Code="EL"/>      <City Name="Fier" Code="FR"/>      <City Name="Gjirokaster" Code="GJ"/>      <City Name="Korce" Code="KO"/>      <City Name="Kukes" Code="KU"/>      <City Name="Lezhe" Code="LE"/>      <City Name="Shkoder" Code="SH"/>      <City Name="Tirane" Code="TR"/>      <City Name="Vlore" Code="VL"/>    </State>  </CountryRegion>  <CountryRegion Name="Algeria" Code="DZA">    <State >      <City Name="Adrar" Code="ADR"/>      <City Name="Ain Defla" Code="ADE"/>      <City Name="Ain Temouchent" Code="ATE"/>      <City Name="Alger" Code="ALG"/>      <City Name="Annaba" Code="AAE"/>      <City Name="Batna" Code="BAT"/>      <City Name="Bechar" Code="BEC"/>      <City Name="Bejaia" Code="BJA"/>      <City Name="Biskra" Code="BIS"/>      <City Name="Blida" Code="BLI"/>      <City Name="Bordj Bou Arreridj" Code="BOR"/>      <City Name="Bouira" Code="BOA"/>      <City Name="Boumerdes" Code="BOU"/>      <City Name="Chlef" Code="CHL"/>      <City Name="Constantine" Code="CZL"/>      <City Name="Djelfa" Code="DJE"/>      <City Name="El Bayadh" Code="EBA"/>      <City Name="El Oued" Code="EOU"/>      <City Name="El Tarf" Code="ETA"/>      <City Name="Ghardaia" Code="GHA"/>      <City Name="Guelma" Code="GUE"/>      <City Name="Illizi" Code="ILL"/>      <City Name="Jijel" Code="JIJ"/>      <City Name="Khenchela" Code="KHE"/>      <City Name="Laghouat" Code="LAG"/>      <City Name="Mascara" Code="MUA"/>      <City Name="Medea" Code="MED"/>      <City Name="Mila" Code="MIL"/>      <City Name="Mostaganem" Code="MOS"/>      <City Name="Msila" Code="MSI"/>      <City Name="Naama" Code="NAA"/>      <City Name="Oran" Code="ORA"/>      <City Name="Ouargla" Code="OUA"/>      <City Name="Oum el Bouaghi" Code="OEB"/>      <City Name="Relizane" Code="REL"/>      <City Name="Saida" Code="SAI"/>      <City Name="Setif" Code="SET"/>      <City Name="Sidi Bel Abbes" Code="SBA"/>      <City Name="Skikda" Code="SKI"/>      <City Name="Souk Ahras" Code="SAH"/>      <City Name="Tamanghasset" Code="TAM"/>      <City Name="Tebessa" Code="TEB"/>      <City Name="Tiaret" Code="TIA"/>      <City Name="Tindouf" Code="TIN"/>      <City Name="Tipaza" Code="TIP"/>      <City Name="Tissemsilt" Code="TIS"/>      <City Name="Tizi Ouzou" Code="IOU"/>      <City Name="Tlemcen" Code="TLE"/>    </State>  </CountryRegion></Location>

 

  <CountryRegion Name="United States Minor Outlying Islands" Code="UMI" />  <CountryRegion Name="Uruguay" Code="URY">    <State >      <City Name="Artigas" Code="AR"/>      <City Name="Canelones" Code="CA"/>      <City Name="Cerro Largo" Code="CL"/>      <City Name="Colonia" Code="CO"/>      <City Name="Durazno" Code="DU"/>      <City Name="Flores" Code="FS"/>      <City Name="Florida" Code="FA"/>      <City Name="Lavalleja" Code="LA"/>      <City Name="Maldonado" Code="MA"/>      <City Name="Montevideo" Code="MO"/>      <City Name="Paysandu" Code="PA"/>      <City Name="Rio Negro" Code="RN"/>      <City Name="Rivera" Code="RV"/>      <City Name="Rocha" Code="RO"/>      <City Name="Salto" Code="SL"/>      <City Name="San Jose" Code="SJ"/>      <City Name="Soriano" Code="SO"/>      <City Name="Tacuarembo" Code="TAW"/>      <City Name="Treinta y Tres" Code="TT"/>    </State>  </CountryRegion>

你这样贴是没有用的！
因为经复制粘贴和 CSDN 编辑器处理后，一些特殊字符就不复存在了
你要么贴出 echo base64_encode(file_get_contents('你的xml文件')); 的结果
要么就把你的xml文件放到网盘上

这种东西？

77u/PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0idXRmLTgiPz4NCjxMb2NhdGlvbj4NCiAgPENvdW50cnlSZWdpb24gTmFtZT0iQWZnaGFuaXN0YW4iIENvZGU9IkFGRyI+DQogICAgPFN0YXRlID4NCiAgICAgIDxDaXR5IE5hbWU9IkhlcmF0IiBDb2RlPSJIRUEiLz4NCiAgICAgIDxDaXR5IE5hbWU9IkthYnVsIiBDb2RlPSJLQkwiLz4NCiAgICAgIDxDaXR5IE5hbWU9IkthbmRhaGFyIiBDb2RlPSJLREgiLz4NCiAgICAgIDxDaXR5IE5hbWU9Ik1hemFyLWkgU2hhcmlmIiBDb2RlPSJNWlIiLz4NCiAgICA8L1N0YXRlPg0KICA8L0NvdW50cnlSZWdpb24+DQogIDxDb3VudHJ5UmVnaW9uIE5hbWU9IkFsYW5kIGxzbGFuZHMiIENvZGU9IkFMQSIgLz4NCiAgPENvdW50cnlSZWdpb2

http://pan.baidu.com/s/1kTCCDtd
网盘地址

太大了？贴不下？

你贴出的部分没有发现异常

太大了？贴不下？

你贴出的部分没有发现异常

恩比较大  有部分出现了异常
我在楼上贴了网盘文件地址

$s = file_get_contents('D:\我的文档\Downloads\全球各国省市（英文版）.xml');preg_match_all('/United.+/m', $s, $m);foreach($m[0] as $v) {  echo "$v\n";  echo bin2hex($v), PHP_EOL;}

United  Arab  Emirates" Code="ARE">
556e69746564 c2a041726162 c2a0456d6972617465732220436f64653d22415245223e0d
United Kingdom" Code="GBR">
556e69746564c2a04b696e67646f6d2220436f64653d22474252223e0d
United States" Code="USA">
556e69746564c2a05374617465732220436f64653d22555341223e0d
United States Minor Outlying Islands" Code="UMI" />
556e69746564c2a0537461746573c2a04d696e6f72c2a04f75746c79696e67c2a049736c616e64732220436f64653d22554d4922202f3e0d

看到了吧？那可不是空格哟，虽然表现一样

而反过来查

preg_match_all('/.+\xc2\xa0.+/m', $s, $m);foreach($m[0] as $v) {  echo "$v\n";  echo bin2hex($v), PHP_EOL;}

就更多了

在 gbk 环境中可看到

      <City Name="La?Rioja" Code="IRJ"/>      <City Name="San?Luis" Code="LUQ"/>      <City Name="Santiago?del?Estero" Code="SDE"/>  <CountryRegion Name="Ascension?Island" Code="ASC" />    <State Name="New?South?Wales" Code="NSW">    <State Name="Northern?Territory" Code="NT">    <State Name="South?Australia" Code="SA">    <State Name="Western?Australia" Code="WA">      <City Name="Lower?Austria" Code="LAU"/>      <City Name="Upper?Austria" Code="UAU"/>      <City Name="Orange?Walk" Code="OW"/>      <City Name="Stann?Creek" Code="SC"/>      <City Name="El?Alto" Code="ALT"/>      <City Name="El?Beni" Code="BEN"/>      <City Name="La?Paz" Code="LPB"/>      <City Name="Santa?Cruz" Code="SRZ"/>  <CountryRegion Name="Bosnia?and?Herzegovina" Code="BIH">  <CountryRegion Name="Bouvet?Island" Code="BVT" />      <City Name="Espirito?Santo" Code="ES"/>      <City Name="Mato?Grosso" Code="MT"/>      <City Name="Mato?Grosso?do?Sul" Code="MS"/>      <City Name="Minas?Gerais" Code="MG"/>      <City Name="Rio?de?Janeiro" Code="RJ"/>      <City Name="Rio?Grande?do?Norte" Code="RN"/>      <City Name="Rio?Grande?do?Sul" Code="RS"/>      <City Name="Santa?Catarina" Code="SC"/>      <City Name="Sao?Paulo" Code="SP"/>  <CountryRegion Name="British?Indian?Ocean?Territory" Code="IOT" />      <City Name="Grad?Sofiya" Code="GSO"/>  <CountryRegion Name="Burkina?Faso" Code="BFA">      <City Name="Bujumbura?Mairie" Code="BM"/>      <City Name="Bujumbura?Rural" Code="BU"/>      <City Name="Banteay?Mean?Chey" Code="BM"/>      <City Name="Bat?Dambang" Code="BA"/>      <City Name="Kampong?Cham" Code="KM"/>      <City Name="Kampong?Chhnang" Code="KZC"/>      <City Name="Kampong?Spoe" Code="KO"/>      <City Name="Kampong?Thum" Code="KZK"/>      <City Name="Kaoh?Kong" Code="KKZ"/>      <City Name="Krong?Keb" Code="KB"/>      <City Name="Krong?Pailin" Code="PL"/>      <City Name="Krong?Preah" Code="KA"/>      <City Name="Mondol?Kiri" Code="MWV"/>      <City Name="Otdar?Mean?Chey" Code="OC"/>      <City Name="Phnum?Penh" Code="PNH"/>      <City Name="Preah?Vihear" Code="PR"/>      <City Name="Prey?Veng" Code="PG"/>      <City Name="Rotanak?Kiri" Code="RBE"/>      <City Name="Siem?Reab" Code="REP"/>      <City Name="Stoeng?Treng" Code="TNX"/>      <City Name="Svay?Rieng" Code="SVR"/>  <CountryRegion Name="Cape?Verde" Code="CPV">      <City Name="Boa?Vista" Code="BV"/>      <City Name="Porto?Novo" Code="PN"/>      <City Name="Ribeira?Grande" Code="RG"/>      <City Name="Santa?Catarina" Code="CA"/>      <City Name="Santa?Cruz" Code="CR"/>      <City Name="Santo?Antao" Code="SA"/>      <City Name="Sao?Domingos" Code="SD"/>      <City Name="Sao?Filipe" Code="SF"/>      <City Name="Sao?Miguel" Code="SM"/>      <City Name="Sao?Nicolau" Code="SN"/>      <City Name="Sao?Vicente" Code="SV"/>  <CountryRegion Name="Cayman?Islands" Code="CYM" />  <CountryRegion Name="Central?African?Republic" Code="CAF">      <City Name="Magallanes?y?Antartica?Chilena" Code="MA"/>      <City Name="Metropolitana?de?Santiago" Code="RM"/>      <City Name="Region?de?Alsen?del?General?Carlos?Ibanez?del" Code="AI"/>      <City Name="Region?de?Antofagasta" Code="AN"/>      <City Name="Region?de?Atacama" Code="AT"/>      <City Name="Region?de?Coquimbo" Code="CO"/>      <City Name="Region?de?la?Araucania" Code="AR"/>      <City Name="Region?de?los?Lagos" Code="LL"/>      <City Name="Region?de?Tarapaca" Code="TA"/>      <City Name="Region?de?Valparaiso" Code="VS"/>      <City Name="Region?del?Biobio" Code="BI"/>      <City Name="Region?del?Maule" Code="ML"/>  <CountryRegion Name="Christmas?Island" Code="CXR" />      <City Name="La?Guajira" Code="GJR"/>      <City Name="Norte?de?Santander" Code="NDS"/>      <City Name="San?Andres?y?Providencia" Code="SAP"/>      <City Name="Valle?del?Cauca" Code="VDC"/>  <CountryRegion Name="Cook?Islands" Code="COK" />  <CountryRegion Name="Costa?Rica" Code="CRI">      <City Name="San?Jose" Code="SJ"/>  <CountryRegion Name="Cote?d&apos;Ivoire" Code="CIV">      <City Name="Vallee?du?Bandama" Code="VB"/>      <City Name="Grad?Zagreb" Code="21"/>      <City Name="Ciego?de?Avila" Code="8"/>      <City Name="Ciudad?de?la?Habana" Code="3"/>      <City Name="Isla?de?la?Juventud" Code="99"/>      <City Name="La?Habana" Code="2"/>      <City Name="Las?Tunas" Code="10"/>      <City Name="Pinar?del?Rio" Code="1"/>      <City Name="Sancti?Spiritus" Code="7"/>      <City Name="Santiago?de?Cuba" Code="13"/>      <City Name="Villa?Clara" Code="5"/>  <CountryRegion Name="Czech?Republic" Code="CZE">      <City Name="Ali?Sabih" Code="S"/>  <CountryRegion Name="Dominican?Republic" Code="DOM" />      <City Name="El?Oro" Code="O"/>      <City Name="Los?Rios" Code="R"/>      <City Name="Napo,?Orellana" Code="D"/>      <City Name="Shubra?al?Khaymah" Code="SKH"/>  <CountryRegion Name="El?Salvador" Code="SLV">      <City Name="Centro?Sur" Code="CS"/>      <City Name="La?Libertad" Code="LB"/>      <City Name="La?Paz" Code="PZ"/>      <City Name="La?Union" Code="UN"/>      <City Name="San?Miguel" Code="SM"/>      <City Name="San?Salvador" Code="SS"/>      <City Name="San?Vicente" Code="SV"/>      <City Name="Santa?Ana" Code="SA"/>      <City Name="Debubawi?Keyih?Bahri" Code="DK"/>      <City Name="Gash?Barka" Code="BR"/>      <City Name="Semenawi?Keyih?Bahri" Code="SK"/>      <City Name="Adis?abeba" Code="AA"/>      <City Name="Binshangul?Gumuz" Code="BG"/>      <City Name="Dire?Dawa" Code="DD"/>      <City Name="Gambela?Hizboch" Code="GB"/>      <City Name="Hareri??Hizb" Code="HR"/>      <City Name="YeDebub?Biheroch" Code="SN"/>  <CountryRegion Name="Falkland?Islands" Code="FLK" />  <CountryRegion Name="Faroe?Islands" Code="FRO" />  <CountryRegion Name="Fiji?Islands" Code="FJI" />  <CountryRegion Name="French?Polynesia" Code="PYF" />  <CountryRegion Name="French?Guiana" Code="GUF" />  <CountryRegion Name="French?Southern?and?Antarctic?Lands" Code="ATF" />      <City Name="Brong?Ahafo" Code="BA"/>      <City Name="Greater?Accra" Code="GA"/>      <City Name="Upper?East" Code="UE"/>      <City Name="Upper?West" Code="UW"/>      <City Name="Alta?Verapaz" Code="AV"/>      <City Name="Baja?Verapaz" Code="BV"/>      <City Name="El?Progreso" Code="PR"/>      <City Name="San?Marcos" Code="SM"/>      <City Name="Santa?Rosa" Code="SR"/>      <City Name="Villa?Nueva" Code="VIN"/>      <City Name="East?Berbice-Corentyne" Code="EC"/>      <City Name="Essequibo?Islands-West?Demerara" Code="EW"/>      <City Name="Upper?Demerara-Berbice" Code="UD"/>      <City Name="Upper?Takutu-Upper?Essequibo" Code="UT"/>  <CountryRegion Name="Heard?Island?and?McDonald?Islands" Code="HMD" />      <City Name="El?Paraiso" Code="PA"/>      <City Name="Francisco?Morazan" Code="FM"/>      <City Name="Gracias?a?Dios" Code="GD"/>      <City Name="Islas?de?la?Bahia" Code="IB"/>      <City Name="La?Paz" Code="PZ"/>      <City Name="Santa?Barbara" Code="SB"/>      <City Name="Daerah?Istimewa?Yogyakarta" Code="YO"/>.......

真的贴不下了

谢谢版主，问题已经解决，以前没碰到过这种问题，今天又学到了一招。