ERROR 1406 (22001): Data too long for column 'c_name' at row 1&&ERROR 1366 (HY000): Incorrect string

分手后的思念是犯贱 2022-03-21 14:36 255阅读 0赞

> 问题描述：

mysql> use flyingcat;
    Database changed
    mysql> source E:\wamp64\www\flyingcat\install\data.sql
    
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE6\x94\xBE\xE5\x89...' for column 'name' at row 1
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE6\x94\xBE\xE9\xA1...' for column 'name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xA7\x86\xE7\x9B\xB4\xE6...' for column 'name' at row 1
    
    ERROR 1406 (22001): Data too long for column 's_name' at row 1
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1366 (HY000): Incorrect string value: '\xA7\x86' for column 's_name' at row 1
    
    ERROR 1366 (HY000): Incorrect string value: '\xAE\xE9\x80\x9A\xE4\xBC...' for column 'ug_name' at row 1
    
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE8\x88\x9E\xE7\xBE...' for column 'd_name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE8\x88\x9E\xE7\xBE...' for column 'd_name' at row 1
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1366 (HY000): Incorrect string value: '\x8820180...' for column 'd_name' at row 1
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.03 sec)
    
    ERROR 1406 (22001): Data too long for column 'c_name' at row 1
    mysql> source E:\wamp64\www\flyingcat\install\data.sql
    Query OK, 0 rows affected (0.00 sec)
    
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE6\x94\xBE\xE5\x89...' for column 'name' at row 1
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1406 (22001): Data too long for column 'name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE6\x94\xBE\xE9\xA1...' for column 'name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xA7\x86\xE7\x9B\xB4\xE6...' for column 'name' at row 1
    
    ERROR 1366 (HY000): Incorrect string value: '\xA7\x86' for column 's_name' at row 1
    
    ERROR 1366 (HY000): Incorrect string value: '\xAE\xE9\x80\x9A\xE4\xBC...' for column 'ug_name' at row 1
    Query OK, 1 row affected (0.01 sec)
    
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE8\x88\x9E\xE7\xBE...' for column 'd_name' at row 1
    ERROR 1366 (HY000): Incorrect string value: '\xAD\xE8\x88\x9E\xE7\xBE...' for column 'd_name' at row 1
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    ERROR 1366 (HY000): Incorrect string value: '\x8820180...' for column 'd_name' at row 1
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.03 sec)
    
    ERROR 1406 (22001): Data too long for column 'c_name' at row 1

> 问题解决：

mysql> set names utf8 ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> source E:\wamp64\www\flyingcat\install\data.sql
    Query OK, 0 rows affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 1 row affected (0.00 sec)
    
    Query OK, 0 rows affected (0.01 sec)
    
    Query OK, 0 rows affected (0.02 sec)
    
    Query OK, 1 row affected (0.01 sec)
    
    mysql>

**看见网上都是改sql-mode，我的字符长度是足够的，所以改不改sql-mode根本不起作用，解决问题的关键一句：**

> mysql> set names utf8 ;  
> 或者  
> CREATE DATABASE IF NOT EXISTS databaseName DEFAULT CHARSET utf8 COLLATE utf8\_general\_ci;  
> 参考文章：  
> [https://blog.csdn.net/waveclouds/article/details/73882962][https_blog.csdn.net_waveclouds_article_details_73882962]

[https_blog.csdn.net_waveclouds_article_details_73882962]: https://blog.csdn.net/waveclouds/article/details/73882962