导入具有未知字符集的数据库，使用哪种排序规则？

Question

我从数据库中获得了一个我试图导入的制表符分隔格式的转储。不幸的是我无法获得CREATE TABLE查询，所以我试图猜测他们使用了什么。

我尝试过使用各种 utf8 和 latin1 排序规则导入，但它们都给了我这些警告

Warning (Code 1366): Incorrect string value: '\xE1\x9A Tom...' for column 'username' at row 28907
Warning (Code 1366): Incorrect string value: '\xE1n Mar...' for column 'username' at row 29105
Warning (Code 1366): Incorrect string value: '\xE1n' for column 'username' at row 29192
Warning (Code 1366): Incorrect string value: '\xE1r G\xE9z...' for column 'username' at row 29313
Warning (Code 1366): Incorrect string value: '\xE7alves' for column 'username' at row 29487
Warning (Code 1366): Incorrect string value: '\xF6res' for column 'username' at row 29728
Warning (Code 1366): Incorrect string value: '\xFE\xF3rsso...' for column 'username' at row 29842
Warning (Code 1366): Incorrect string value: '\xF6l' for column 'username' at row 30117
Warning (Code 1366): Incorrect string value: '\xF3hann ...' for column 'username' at row 30310
Warning (Code 1366): Incorrect string value: '\xFCchner' for column 'username' at row 30432
Warning (Code 1366): Incorrect string value: '\xD6sterr...' for column 'username' at row 30486
Warning (Code 1366): Incorrect string value: '\xFCrgenK...' for column 'username' at row 30795
Warning (Code 1366): Incorrect string value: '\xE1n Les...' for column 'username' at row 30846
Warning (Code 1366): Incorrect string value: '\xE0tz \xD1\xD6...' for column 'username' at row 31002
Warning (Code 1366): Incorrect string value: '\xE9n Fer...' for column 'username' at row 31015
Warning (Code 1366): Incorrect string value: '\xE4cker' for column 'username' at row 31048
Warning (Code 1366): Incorrect string value: '\xDFe' for column 'username' at row 31505
Warning (Code 1366): Incorrect string value: '\xE1nchez...' for column 'username' at row 31762
Warning (Code 1366): Incorrect string value: '\x9A Stre...' for column 'username' at row 32144
Warning (Code 1366): Incorrect string value: '\x9A Stre...' for column 'username' at row 32145
Warning (Code 1366): Incorrect string value: '\xF6rk' for column 'username' at row 32309

另一个表

Warning (Code 1366): Incorrect string value: '\xC5\x91-esz...' for column 'clear' at row 180175
Warning (Code 1366): Incorrect string value: '\xC5\x91dies...' for column 'clear' at row 180181
Warning (Code 1366): Incorrect string value: '\xD0\xB04510...' for column 'clear' at row 324698
Warning (Code 1366): Incorrect string value: '\xC5\x9Fem' for column 'clear' at row 332177
Warning (Code 1366): Incorrect string value: '\xC5\x91' for column 'clear' at row 333689
Warning (Code 1366): Incorrect string value: '\xC5\x91' for column 'clear' at row 420229
Warning (Code 1366): Incorrect string value: '\xC5\xBAdzio...' for column 'clear' at row 492435
Warning (Code 1366): Incorrect string value: '\xD1\x8A\xD0\xB9\xD1\x8C' for column 'clear' at row 512071
Warning (Code 1366): Incorrect string value: '\xC5\x91_000...' for column 'clear' at row 516010
Warning (Code 1366): Incorrect string value: '\xC5\x84' for column 'clear' at row 518718
Warning (Code 1366): Incorrect string value: '\xC5\x82gorz...' for column 'clear' at row 518879
Warning (Code 1366): Incorrect string value: '\xC5\x82!' for column 'clear' at row 518984
Warning (Code 1366): Incorrect string value: '\xE4\xB8\xAD\xE5\x9B\xBD' for column 'clear' at row 578301
Warning (Code 1366): Incorrect string value: '\xC5\x9Fifre...' for column 'clear' at row 622762
Warning (Code 1366): Incorrect string value: '\xC5\x9B\xC4\x87' for column 'clear' at row 651931
Warning (Code 1366): Incorrect string value: '\xE0\xB8\xB4\xE0\xB8\x99...' for column 'clear' at row 653857
Warning (Code 1366): Incorrect string value: '\xE0\xB9\x81\xE0\xB9\x89...' for column 'clear' at row 655241
Warning (Code 1366): Incorrect string value: '\xD0\xBA\xD0\xB0\xD0\xBA...' for column 'clear' at row 696299
Warning (Code 1366): Incorrect string value: '\xD0\x95\xD0\xBA\xD0\xB0...' for column 'clear' at row 696338
Warning (Code 1366): Incorrect string value: '\xD0\x9F\xD1\x80\xD0\xB8...' for column 'clear' at row 696389
Warning (Code 1366): Incorrect string value: '\xD1\x85\xD0\xB5\xD1\x80...' for column 'clear' at row 696558
Warning (Code 1366): Incorrect string value: '\xD1\x80\xD0\xB5\xD0\xB3...' for column 'clear' at row 696629
Warning (Code 1366): Incorrect string value: '\xD1\x85\xD1\x83\xD0\xB9...' for column 'clear' at row 696637
Warning (Code 1366): Incorrect string value: '\xD0\xBF\xD0\xBE\xD1\x80...' for column 'clear' at row 697145
Warning (Code 1366): Incorrect string value: '\xD0\xB2\xD0\xB8\xD1\x82...' for column 'clear' at row 697595
Warning (Code 1366): Incorrect string value: '\xD0\xA0\xC2\xB1\xD0\xA0...' for column 'clear' at row 697771
Warning (Code 1366): Incorrect string value: '\xD0\x91\xD0\xBB\xD0\xB0...' for column 'clear' at row 698058
Warning (Code 1366): Incorrect string value: '\xDF\xA7' for column 'clear' at row 705955
Warning (Code 1366): Incorrect string value: '\xDF\xB4' for column 'clear' at row 705965
Warning (Code 1366): Incorrect string value: '\xDF\x80' for column 'clear' at row 705972
Warning (Code 1366): Incorrect string value: '\xDC\xA7' for column 'clear' at row 707670
Warning (Code 1366): Incorrect string value: '\xDC\xB4' for column 'clear' at row 707680
Warning (Code 1366): Incorrect string value: '\xDC\x80' for column 'clear' at row 707687
Warning (Code 1366): Incorrect string value: '\xD0\xB1\xD1\x83\xD1\x80...' for column 'clear' at row 772209
Warning (Code 1366): Incorrect string value: '\xD9\x87\xD9\x88\xD9\x84...' for column 'clear' at row 773723
Warning (Code 1366): Incorrect string value: '\xD9\x86\xD8\xAF\xD9\x8A...' for column 'clear' at row 773754
Warning (Code 1366): Incorrect string value: '\xC4\x97' for column 'clear' at row 800748
Warning (Code 1366): Incorrect string value: '\xC5\x9Blina' for column 'clear' at row 800966
Warning (Code 1366): Incorrect string value: '\xC4\x97' for column 'clear' at row 801332
Warning (Code 1366): Incorrect string value: '\xD0\xBC\xD0\xBE\xD0\xBE...' for column 'clear' at row 801512
Warning (Code 1366): Incorrect string value: '\xC5\xBCen' for column 'clear' at row 801552
Warning (Code 1366): Incorrect string value: '\xC4\xB1' for column 'clear' at row 826638
Warning (Code 1366): Incorrect string value: '\xC4\x9B\xC5\xA1\xC4\x8D...' for column 'clear' at row 848786
Warning (Code 1366): Incorrect string value: '\xC4\x8Dky' for column 'clear' at row 931036
Warning (Code 1366): Incorrect string value: '\xC4\x9B\xC5\xA1na' for column 'clear' at row 931643
Warning (Code 1366): Incorrect string value: '\xC4\x8Dko' for column 'clear' at row 932973
Warning (Code 1366): Incorrect string value: '\xC4\xBE' for column 'clear' at row 933156
Warning (Code 1366): Incorrect string value: '\xC4\x9B\xC5\xA1\xC4\x8D...' for column 'clear' at row 933795
Warning (Code 1366): Incorrect string value: '\xE2\x97\x8F\xE2\x97\x8F...' for column 'clear' at row 952359
Warning (Code 1366): Incorrect string value: '\xE2\x97\x8F\xE2\x97\x8F...' for column 'clear' at row 952559
Warning (Code 1366): Incorrect string value: '\xD0\xB9\xD1\x8B\xD0\xB1' for column 'clear' at row 1000672
Warning (Code 1366): Incorrect string value: '\xC5\x9B' for column 'clear' at row 1046203
Warning (Code 1366): Incorrect string value: '\xC5\xBCeron...' for column 'clear' at row 1075021
Warning (Code 1366): Incorrect string value: '\xC4\xB1n' for column 'clear' at row 1077277
Warning (Code 1366): Incorrect string value: '\xC5\x9Bko12...' for column 'clear' at row 1078692
Warning (Code 1366): Incorrect string value: '\xC5\x9B444' for column 'clear' at row 1078953
Warning (Code 1366): Incorrect string value: '\xC4\xB1ktan' for column 'clear' at row 1082672
Warning (Code 1366): Incorrect string value: '\xC5\x82ar\xC3\xB3...' for column 'clear' at row 1084747
Warning (Code 1366): Incorrect string value: '\xC5\x82czy\xC5...' for column 'clear' at row 1085133
Warning (Code 1366): Incorrect string value: '\xC5\x84stwo...' for column 'clear' at row 1086168
Warning (Code 1366): Incorrect string value: '\xC5\x82abym...' for column 'clear' at row 1086203
Warning (Code 1366): Incorrect string value: '\xE2\x97\x8F\xE2\x97\x8F...' for column 'clear' at row 1086486
Warning (Code 1366): Incorrect string value: '\xE2\x97\x8F\xE2\x97\x8F...' for column 'clear' at row 1086506
Warning (Code 1366): Incorrect string value: '\xE2\x97\x8F\xE2\x97\x8F...' for column 'clear' at row 1086508

我似乎无法弄清楚他们使用的排序规则，并且没有想法。我也不是字符编码专家，这使事情变得更加困难。其他问题提到使用一些 Microsoft 编码来存储奇怪但没有警告的内容。然而，我对此感到紧张，因为它是一个用户名列，并且存储错误或显示奇怪不是一个选项。 “清晰”栏也一样

有什么想法吗？

Answer 1

如果您无法使用 Pelshoff 建议解决问题，我认为您可以在这种情况下使用 utf8_general_ci 或 utf8_unicode_ci 。这些排序规则是最受欢迎的...

Answer 2

尝试使用 mb_detect_encoding 等函数找出字符编码。除此之外，祝你好运！

http://php.net/manual/en/function.mb-detect -编码.php

Answer 3

好吧，如果您不知道它来自哪个国家，甚至不知道它的最初目的……它本身也可能已损坏。我的意思是，如果它有很多很多列，并且没有错误，我会先将它放入电子表格中，或者尝试处理大数据的NOTETAB！文本文件并进行查看。