Why is the PHPMyAdmin import truncating text field

I had some problems with dodgy characters in my database, thanks to a poorly-coded filter script (created by yours truly, of course!).

I fixed the data by exporting it from PHPMyAdmin as an SQL file, then painstakingly CTRL+H search & replacing the incorrect characters for the right one. I then re-imported the SQL file (well, three separate files) using PHPMyAdmin.

This has worked fine for most of the data, but on a few posts, they seem to be completely cut off.

meta_id     post_id     meta_key            meta_value
162227      264847      water_chemistry     <strong>Temperature</strong>:
162228      264847      diet                <em>Homaloptera</em>
162229      264847      behaviour           <p>Not an aggressive fish although its particular requirements limit the choice of suitable tankmates.
162218      264847      maintenance         <p>Most importantly the water must be clean and well-oxygenated so we suggest the use of an over-sized

For example, this species profile (text above) should've had the following UPDATE statements applied to it:

UPDATE `wp_postmeta` SET `meta_id` = 162228,`post_id` = 264847,`meta_key` = 'diet',`meta_value` = '<em>Homaloptera</em> spp. are specialised grazers feeding on <a href="/glossary/b/biofilm" rel="/glossary/b/biofilm?hover=true">biofilm</a>, small crustaceans, insect <a href="/glossary/l/larvae" rel="/glossary/l/larvae?hover=true">larvae</a> and other invertebrates. In captivity some sinking dried foods may be accepted but regular meals of live or frozen <em><a href="/glossary/D/Daphnia" rel="/glossary/D/Daphnia?hover=true">Daphnia</a></em>, <em><a href="/glossary/A/Artemia" rel="/glossary/A/Artemia?hover=true">Artemia</a></em>, <a href="/glossary/b/bloodworm" rel="/glossary/b/bloodworm?hover=true">bloodworm</a>, etc. are essential for the maintenance of good health, and it''s highly preferable if the <a href="/glossary/t/tank" rel="/glossary/t/tank?hover=true">tank</a> contains rock and other solid surfaces with growths of <a href="/glossary/a/algae" rel="/glossary/a/algae?hover=true">algae</a> and other <a href="/glossary/a/aufwuchs" rel="/glossary/a/aufwuchs?hover=true">aufwuchs</a>.\r\n\r\nBalitorids are often seen on sale in an emaciated state which can be difficult to correct. A good dealer will have done something about this prior to sale but if you decide to take a chance with severely weakened specimens they''ll initially require a continual, easily-obtainable source of suitable foods in the absence of competitors if they''re to recover.' WHERE `wp_postmeta`.`meta_id` = 162228;

UPDATE `wp_postmeta` SET `meta_id` = 162227,`post_id` = 264847,`meta_key` = 'water_chemistry',`meta_value` = '<strong>Temperature</strong>: [temp]\r\n\r\n<strong>pH</strong>: [pH]\r\n\r\n<strong>Hardness</strong>: [hardness]' WHERE `wp_postmeta`.`meta_id` = 162227;

UPDATE `wp_postmeta` SET `meta_id` = 162229,`post_id` = 264847,`meta_key` = 'behaviour',`meta_value` = '<p>Not an aggressive fish although its particular requirements limit the choice of suitable tankmates. <a href="/glossary/s/species" rel="/glossary/s/species?hover=true">Species</a> inhabiting similar environments include <em>Barilius</em>, <em>Discherodontus</em>, <em>Garra</em>, larger <em>Devario</em>, some <em>Rasbora</em>, gobies of the <a href="/glossary/g/genera" rel="/glossary/g/genera?hover=true">genera</a> <em>Rhinogobius</em>, <em>Sicyopterus</em> and <em>Stiphodon</em> plus <em>Glyptothorax</em>, <em>Akysis</em> and <em>Oreoglanis</em> spp. catfishes.\r\n\r\nMany loaches from the <a href="/glossary/f/family" rel="/glossary/f/family?hover=true">family</a> Nemacheilidae and most from Balitoridae are also suitable although harmless squabbles may occur with the latter group in particular. Research your choices before purchase to be sure.\r\n\r\nIt''s found living in aggregations in nature so buy six or more to see it at its best as when kept singly or in pairs/trios it''s less bold. The interaction between individuals is also interesting to watch and a group will typically arrange themselves close to one another facing directly into the water flow at certain times of day.</p>' WHERE `wp_postmeta`.`meta_id` = 162229;

UPDATE `wp_postmeta` SET `meta_id` = 162218,`post_id` = 264847,`meta_key` = 'maintenance',`meta_value` = '<p>Most importantly the water must be clean and well-oxygenated so we suggest the use of an over-sized <a href="/glossary/f/filter" rel="/glossary/f/filter?hover=true">filter</a> as a minimum requirement. Turnover should ideally be 10-15 times per hour so additional powerheads, airstones, etc., should be employed to achieve the desired flow and <a href="/glossary/o/oxygenation" rel="/glossary/o/oxygenation?hover=true">oxygenation</a> if necessary.\r\n\r\n<a href="/glossary/b/base" rel="/glossary/b/base?hover=true">Base</a> <a href="/glossary/s/substrate" rel="/glossary/s/substrate?hover=true">substrate</a> can either be of <a href="/glossary/g/gravel" rel="/glossary/g/gravel?hover=true">gravel</a>, <a href="/glossary/s/sand" rel="/glossary/s/sand?hover=true">sand</a> or a mixture of both to which should be added a layer of water-worn rocks and pebbles of varying sizes. Driftwood roots and branches are also suitable and although rarely a feature of the natural <a href="/glossary/h/habitat" rel="/glossary/h/habitat?hover=true">habitat</a> <a href="/glossary/a/aquatic" rel="/glossary/a/aquatic?hover=true">aquatic</a> plants from adaptable genera such as <em>Microsorum</em>, <em>Crinum</em> and <em>Anubias</em> spp. can also be added. The latter are particularly useful as <em>Homaloptera</em> spp. appear to enjoy resting on their leaves.\r\n\r\nSince it needs stable water conditions and feeds on <a href="/glossary/b/biofilm" rel="/glossary/b/biofilm?hover=true">biofilm</a> this species should never be added to a biologically immature set-up, and a tightly-fitting cover is necessary since it can literally climb glass. While regular partial water changes are essential aufwuchs can be allowed to grow on all surfaces except perhaps the viewing pane.</p>' WHERE `wp_postmeta`.`meta_id` = 162218;

Yet for some reason, as you can see with the species profile (and it's exactly the same in the database), most of that text is missing.

Any ideas what might be causing this? The import raises no errors at all.

EDIT

I've tried replacing the \r\n characters with ### just to see if the data imports correctly - it doesn't.

回答1:

In the end, this was an encoding problem. When I originally split my SQL data into two separate files, the new file had a default encoding of ANSI where the original (and my database were UTF-8).

Upon changing the encoding to UTF-8, I saw loads of strange little characters which my text editor couldn't search & replace.

I set the encoding back to ANSI, created a new text document with UTF-8 and simply copy-pasted the information from the ANSI document to the new UTF-8 file.

All of the above was done in Notepad++.

Problem solved - 7 hours of diagnosing the problem; less than 5 seconds to fix.

回答2:

if you are using PHP use mysql_real_escape_string(): this is what is happening

mysql> select "<strong>Temperature</strong>: [temp]\r\n\r\n<strong>pH</strong>:";
+--------------------------------------------------------------+
| <strong>Temperature</strong>: [temp]

<strong>pH</strong>: |
+--------------------------------------------------------------+
| <strong>Temperature</strong>: [temp]

<strong>pH</strong>: |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

so use php function to store the data exactly how it's.

http://dev.mysql.com/doc/refman/5.1/en/mysql-real-escape-string.html