I would not suggest Richies answer, because you are screwing up the data inside the database. You would not fix your problem but try to "hide" it and not being able to perform essential database operations with the crapped data.
If you encounter this error either the data you are sending is not UTF-8 encoded, or your connection is not UTF-8. First, verify, that the data source (a file, ...) really is UTF-8.
Then, check your database connection, you should do this after connecting:
SET NAMES 'utf8'; SET CHARACTER SET utf8;
Next, verify that the tables where the data is stored have the utf8 character set:
SELECT `tables`.`TABLE_NAME`, `collations`.`character_set_name` FROM `information_schema`.`TABLES` AS `tables`, `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY` AS `collations` WHERE `tables`.`table_schema` = DATABASE() AND `collations`.`collation_name` = `tables`.`table_collation` ;
Last, check your database settings:
mysql> show variables like '%colla%'; mysql> show variables like '%charac%';
If source, transport and destination are UTF-8, your problem is gone;)
另外,安装完mysql之后,应该运行一下命令来设置编码为utf-8:
先在命令行运行:status查看编码是不是utf8。
CREATE DATABASE `test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
2、建表的时候
CREATE TABLE `database_user` ( `ID` varchar(40) NOT NULL default '', `UserID` varchar(40) NOT NULL default '', ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
即建库和建表时都使用相同的编码格式。
In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:
-
MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to
CHAR
,VARCHAR
, and theTEXT
types. -
Column definitions for many string data types can include attributes that specify the character set or collation of the column. These attributes apply to the
CHAR
,VARCHAR
, theTEXT
types,ENUM
, andSET
data types:-
The
CHARACTER SET
attribute specifies the character set, and theCOLLATE
attribute specifies a collation for the character set. For example:CREATE TABLE t ( c1 VARCHAR(20) CHARACTER SET utf8, c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs );
This table definition creates a column named
c1
that has a character set ofutf8
with the default collation for that character set, and a column namedc2
that has a character set oflatin1
and a case-sensitive collation.The rules for assigning the character set and collation when either or both of the
CHARACTER SET
andCOLLATE
attributes are missing are described in Section 10.1.3.4, “Column Character Set and Collation”.CHARSET
is a synonym forCHARACTER SET
. -
Specifying the
CHARACTER SET binary
attribute for a character data type causes the column to be created as the corresponding binary data type:CHAR
becomesBINARY
,VARCHAR
becomesVARBINARY
, andTEXT
becomesBLOB
. For theENUM
andSET
data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );
The resulting table has this definition:
CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );
-
The
ASCII
attribute is shorthand forCHARACTER SET latin1
. -
The
UNICODE
attribute is shorthand forCHARACTER SET ucs2
. -
The
BINARY
attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values. (Before MySQL 4.1,BINARY
caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multibyte character sets.)
-
-
Character column sorting and comparison are based on the character set assigned to the column. (Before MySQL 4.1, sorting and comparison were based on the collation of the server character set.) For the
CHAR
,VARCHAR
,TEXT
,ENUM
, andSET
data types, you can declare a column with a binary collation or theBINARY
attribute to cause sorting and comparison to use the underlying character code values rather than a lexical ordering.
Section 10.1, “Character Set Support”, provides additional information about use of character sets in MySQL.
-
[NATIONAL] CHAR[(
M
)] [CHARACTER SETcharset_name
] [COLLATEcollation_name
]A fixed-length string that is always right-padded with spaces to the specified length when stored.
M
represents the column length in characters. The range ofM
is 0 to 255. IfM
is omitted, the length is 1.NoteTrailing spaces are removed when
CHAR
values are retrieved.Before MySQL 5.0.3, a
CHAR
column with a length specification greater than 255 is converted to the smallestTEXT
type that can hold values of the given length. For example,CHAR(500)
is converted toTEXT
, andCHAR(200000)
is converted toMEDIUMTEXT
. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal.In MySQL 5.0.3 and later, a
CHAR
length greater than 255 is illegal and fails with an error:mysql>
CREATE TABLE c1 (col1 INT, col2 CHAR(500));
ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT insteadCHAR
is shorthand forCHARACTER
.NATIONAL CHAR
(or its equivalent short form,NCHAR
) is the standard SQL way to define that aCHAR
column should use some predefined character set. MySQL 4.1 and up usesutf8
as this predefined character set. Section 10.1.3.6, “National Character Set”.The
CHAR BYTE
data type is an alias for theBINARY
data type. This is a compatibility feature.MySQL permits you to create a column of type
CHAR(0)
. This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value.CHAR(0)
is also quite nice when you need a column that can take only two values: A column that is defined asCHAR(0) NULL
occupies only one bit and can take only the valuesNULL
and''
(the empty string). -
[NATIONAL] VARCHAR(
M
) [CHARACTER SETcharset_name
] [COLLATEcollation_name
]A variable-length string.
M
represents the maximum column length in characters. In MySQL 5.0, the range ofM
is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later. The effective maximum length of aVARCHAR
in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used. For example,utf8
characters can require up to three bytes per character, so aVARCHAR
column that uses theutf8
character set can be declared to be a maximum of 21,844 characters. See Section D.7.4, “Limits on Table Column Count and Row Size”.MySQL stores
VARCHAR
values as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. AVARCHAR
column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.NoteBefore 5.0.3, trailing spaces were removed when
VARCHAR
values were stored, which differs from the standard SQL specification.Prior to MySQL 5.0.3, a
VARCHAR
column with a length specification greater than 255 is converted to the smallestTEXT
type that can hold values of the given length. For example,VARCHAR(500)
is converted toTEXT
, andVARCHAR(200000)
is converted toMEDIUMTEXT
. However, this conversion affects trailing-space removal.VARCHAR
is shorthand forCHARACTER VARYING
.NATIONAL VARCHAR
is the standard SQL way to define that aVARCHAR
column should use some predefined character set. MySQL 4.1 and up usesutf8
as this predefined character set. Section 10.1.3.6, “National Character Set”.NVARCHAR
is shorthand forNATIONAL VARCHAR
. -
The
BINARY
type is similar to theCHAR
type, but stores binary byte strings rather than nonbinary character strings.M
represents the column length in bytes. -
The
VARBINARY
type is similar to theVARCHAR
type, but stores binary byte strings rather than nonbinary character strings.M
represents the maximum column length in bytes. -
A
BLOB
column with a maximum length of 255 (28 − 1) bytes. EachTINYBLOB
value is stored using a 1-byte length prefix that indicates the number of bytes in the value. -
TINYTEXT [CHARACTER SET
charset_name
] [COLLATEcollation_name
]A
TEXT
column with a maximum length of 255 (28 − 1) characters. The effective maximum length is less if the value contains multibyte characters. EachTINYTEXT
value is stored using a 1-byte length prefix that indicates the number of bytes in the value. -
A
BLOB
column with a maximum length of 65,535 (216 − 1) bytes. EachBLOB
value is stored using a 2-byte length prefix that indicates the number of bytes in the value.An optional length
M
can be given for this type. If this is done, MySQL creates the column as the smallestBLOB
type large enough to hold valuesM
bytes long. -
TEXT[(
M
)] [CHARACTER SETcharset_name
] [COLLATEcollation_name
]A
TEXT
column with a maximum length of 65,535 (216 − 1) characters. The effective maximum length is less if the value contains multibyte characters. EachTEXT
value is stored using a 2-byte length prefix that indicates the number of bytes in the value.An optional length
M
can be given for this type. If this is done, MySQL creates the column as the smallestTEXT
type large enough to hold valuesM
characters long. -
A
BLOB
column with a maximum length of 16,777,215 (224 − 1) bytes. EachMEDIUMBLOB
value is stored using a 3-byte length prefix that indicates the number of bytes in the value. -
MEDIUMTEXT [CHARACTER SET
charset_name
] [COLLATEcollation_name
]A
TEXT
column with a maximum length of 16,777,215 (224 − 1) characters. The effective maximum length is less if the value contains multibyte characters. EachMEDIUMTEXT
value is stored using a 3-byte length prefix that indicates the number of bytes in the value. -
A
BLOB
column with a maximum length of 4,294,967,295 or 4GB (232 − 1) bytes. The effective maximum length ofLONGBLOB
columns depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGBLOB
value is stored using a 4-byte length prefix that indicates the number of bytes in the value. -
LONGTEXT [CHARACTER SET
charset_name
] [COLLATEcollation_name
]A
TEXT
column with a maximum length of 4,294,967,295 or 4GB (232 − 1) characters. The effective maximum length is less if the value contains multibyte characters. The effective maximum length ofLONGTEXT
columns also depends on the configured maximum packet size in the client/server protocol and available memory. EachLONGTEXT
value is stored using a 4-byte length prefix that indicates the number of bytes in the value. -
ENUM('
value1
','value2
',...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
]An enumeration. A string object that can have only one value, chosen from the list of values
'
,value1
''
,value2
'...
,NULL
or the special''
error value.ENUM
values are represented internally as integers.An
ENUM
column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) A table can have no more than 255 unique element list definitions among itsENUM
andSET
columns considered as a group. For more information on these limits, see Section D.7.5, “Limits Imposed by .frm File Structure”. -
SET('
value1
','value2
',...) [CHARACTER SETcharset_name
] [COLLATEcollation_name
]A set. A string object that can have zero or more values, each of which must be chosen from the list of values
'
,value1
''
,value2
'...
SET
values are represented internally as integers.A
SET
column can have a maximum of 64 distinct members. A table can have no more than 255 unique element list definitions among itsENUM
andSET
columns considered as a group. For more information on this limit, see Section D.7.5, “Limits Imposed by .frm File Structure”.
[mysql]
auto-rehash
To enable autocomplete within the MySQL prompt type:
mysql> \#
After that you can type:
mysql> describe someTableW[TAB]
To get:
mysql> describe someTableWithRidiculousLongName