“잘못된 문자열 값”오류를 수정하는 방법?

“잘못된 문자열 값”오류를 수정하는 방법?

---

잘못된 문자열 값 오류로 인해 응용 프로그램에서 임의의 전자 메일을 삭제하는 경향이 있음을 알았지 만 많은 문자 열을 utf8열 문자 세트와 기본 열 콜 레이션 ( utf8_general_ci) 을 사용하도록 전환 하여 허용했습니다. 이로 인해 대부분의 오류가 해결되었으며 비 라틴 전자 메일에 부딪히면 응용 프로그램에서 SQL 오류가 발생하지 않습니다.

그럼에도 불구하고 일부 이메일은 여전히 ​​프로그램이 잘못된 문자열 값 errrors에 도달하도록합니다. (Incorrect string value: '\xE4\xC5\xCC\xC9\xD3\xD8...' for column 'contents' at row 1)

내용 열은 열 문자 MEDIUMTEXT집합과 열 조합을 사용 하는 데이터 형식입니다 . 이 열에서 토글 할 수있는 플래그가 없습니다.utf8utf8_general_ci

절대적으로 필요한 경우가 아니면 응용 프로그램 소스 코드를 만지거나 보지 않으려는 것을 명심하십시오.

• 그 오류의 원인은 무엇입니까? (예, 이메일에 임의의 쓰레기가 가득 차 있다는 것을 알고 있지만 utf8은 꽤 관대하다고 생각했습니다)
• 어떻게 고칠 수 있습니까?
• 그러한 수정의 영향은 무엇입니까?

내가 생각한 것 중 하나는 바이너리 플래그를 켜고 utf8 varchar ([일부 큰 숫자])로 바꾸는 것이지만 MySQL에 익숙하지 않으며 그러한 수정이 의미가 있는지 전혀 모른다.

---

"\xE4\xC5\xCC\xC9\xD3\xD8"유효한 UTF-8이 아닙니다. 파이썬을 사용하여 테스트 :

>>> "\xE4\xC5\xCC\xC9\xD3\xD8".decode("utf-8")
...
UnicodeDecodeError: 'utf8' codec can't decode bytes in position 0-2: invalid data

데이터베이스 내에서 디코딩 오류를 피하는 방법을 찾고 있다면 cp1252 인코딩 (일명 "Windows-1252", "Windows Western European")이 가장 허용되는 인코딩입니다. 모든 바이트 값은 유효한 코드 포인트입니다.

물론 더 이상 진짜 UTF-8이나 다른 비 CP1252 인코딩을 이해하지는 않지만 너무 걱정하지 않는 것처럼 들립니까?

---

데이터베이스 내부의 데이터를 망쳐 놓고 있기 때문에 Richies의 대답을 제안하지 않습니다. 문제점을 수정하지는 않지만 문제점을 "숨기려고"하고 랩된 데이터로 필수 데이터베이스 조작을 수행 할 수 없습니다.

이 오류가 발생하면 전송중인 데이터가 UTF-8로 인코딩되지 않았거나 연결이 UTF-8이 아닙니다. 먼저 데이터 소스 (파일, ...)가 실제로 UTF-8인지 확인하십시오.

그런 다음 데이터베이스 연결을 확인하십시오. 연결 한 후에이 작업을 수행해야합니다.

SET NAMES 'utf8';
SET CHARACTER SET utf8;

다음으로, 데이터가 저장된 테이블에 utf8 문자 세트가 있는지 확인하십시오.

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`
;

마지막으로 데이터베이스 설정을 확인하십시오.

mysql> show variables like '%colla%';
mysql> show variables like '%charac%';

소스, 전송 및 대상이 UTF-8이면 문제가 해결 된 것입니다.)

---

MySQL의 utf-8 유형은 실제로 적절한 utf-8이 아닙니다. 문자 당 최대 3 바이트 만 사용하며 기본 다국어 평면 (이모티콘, 아스트랄 평면 등) 만 지원합니다.

더 높은 유니 코드 평면의 값을 저장해야하는 경우 utf8mb4 인코딩 이 필요합니다 .

---

테이블과 필드의 인코딩이 잘못되었습니다. 그러나 UTF-8로 변환 할 수 있습니다.

ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

---

오늘 UTF-8 문자 대신 원시 바이트를 저장하는 'LONGBLOB'유형으로 열을 변경 하여이 문제를 해결했습니다.

이 작업의 유일한 단점은 인코딩을 직접 관리해야한다는 것입니다. 응용 프로그램의 한 클라이언트가 UTF-8 인코딩을 사용하고 다른 클라이언트가 CP1252를 사용하는 경우 전자 메일이 잘못된 문자로 전송 될 수 있습니다. 이를 피하려면 모든 애플리케이션 에서 항상 동일한 인코딩 (예 : UTF-8)을 사용하십시오 .

TEXT / LONGTEXT와 BLOB / LONGBLOB의 차이점에 대한 자세한 내용은 이 페이지 http://dev.mysql.com/doc/refman/5.0/en/blob.html 을 참조하십시오. 웹에서이 두 가지를 논의하는 다른 주장들도 많이 있습니다.

---

일반적으로 인코딩 / 콜 레이션이 호환되지 않는 열에 문자열을 삽입 할 때 발생합니다.

I got this error when I had TRIGGERs, which inherit server's collation for some reason. And mysql's default is (at least on Ubuntu) latin-1 with swedish collation. Even though I had database and all tables set to UTF-8, I had yet to set my.cnf:

/etc/mysql/my.cnf :

[mysqld]
character-set-server=utf8
default-character-set=utf8

And this must list all triggers with utf8-*:

select TRIGGER_SCHEMA, TRIGGER_NAME, CHARACTER_SET_CLIENT, COLLATION_CONNECTION, DATABASE_COLLATION from information_schema.TRIGGERS

And some of variables listed by this should also have utf-8-* (no latin-1 or other encoding):

show variables like 'char%';

---

First check if your default_character_set_name is utf8.

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "DBNAME";

If the result is not utf8 you must convert your database. At first you must save a dump.

To change the character set encoding to UTF-8 for all of the tables in the specified database, type the following command at the command line. Replace DBNAME with the database name:

mysql --database=DBNAME -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' | mysql --database=DBNAME

To change the character set encoding to UTF-8 for the database itself, type the following command at the mysql> prompt. Replace DBNAME with the database name:

ALTER DATABASE DBNAME CHARACTER SET utf8 COLLATE utf8_general_ci;

You can now retry to to write utf8 character into your database. This solution help me when i try to upload 200000 row of csv file into my database.

---

Although your collation is set to utf8_general_ci, I suspect that the character encoding of the database, table or even column may be different.

ALTER TABLE tabale_name MODIFY COLUMN column_name VARCHAR(255)  
CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

---

That error means that either you have the string with incorrect encoding (e.g. you're trying to enter ISO-8859-1 encoded string into UTF-8 encoded column), or the column does not support the data you're trying to enter.

In practice, the latter problem is caused by MySQL UTF-8 implementation that only supports UNICODE characters that need 1-3 bytes when represented in UTF-8. See "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC? for details.

---

I got a similar error (Incorrect string value: '\xD0\xBE\xDO\xB2. ...' for 'content' at row 1). I have tried to change character set of column to utf8mb4 and after that the error has changed to 'Data too long for column 'content' at row 1'.
It turned out that mysql shows me wrong error. I turned back character set of column to utf8 and changed type of the column to MEDIUMTEXT. After that the error disappeared.
I hope it helps someone.
By the way MariaDB in same case (I have tested the same INSERT there) just cut a text without error.

---

The solution for me when running into this Incorrect string value: '\xF8' for column error using scriptcase was to be sure that my database is set up for utf8 general ci and so are my field collations. Then when I do my data import of a csv file I load the csv into UE Studio then save it formatted as utf8 and Voila! It works like a charm, 29000 records in there no errors. Previously I was trying to import an excel created csv.

---

I have tried all of the above solutions (which all bring valid points), but nothing was working for me.

Until I found that my MySQL table field mappings in C# was using an incorrect type: MySqlDbType.Blob . I changed it to MySqlDbType.Text and now I can write all the UTF8 symbols I want!

p.s. My MySQL table field is of the "LongText" type. However, when I autogenerated the field mappings using MyGeneration software, it automatically set the field type as MySqlDbType.Blob in C#.

Interestingly, I have been using the MySqlDbType.Blob type with UTF8 characters for many months with no trouble, until one day I tried writing a string with some specific characters in it.

Hope this helps someone who is struggling to find a reason for the error.

---

I added binary before the column name and solve the charset error.

insert into tableA values(binary stringcolname1);

---

Hi i also got this error when i use my online databases from godaddy server i think it has the mysql version of 5.1 or more. but when i do from my localhost server (version 5.7) it was fine after that i created the table from local server and copied to the online server using mysql yog i think the problem is with character set

Screenshot Here

---

To fix this error I upgraded my MySQL database to utf8mb4 which supports the full Unicode character set by following this detailed tutorial. I suggest going through it carefully, because there are quite a few gotchas (e.g. the index keys can become too large due to the new encodings after which you have to modify field types).

---

There's good answers in here. I'm just adding mine since I ran into the same error but it turned out to be a completely different problem. (Maybe on the surface the same, but a different root cause.)

For me the error happened for the following field:

@Column(nullable = false, columnDefinition = "VARCHAR(255)")
private URI consulUri;

This ends up being stored in the database as a binary serialization of the URI class. This didn't raise any flags with unit testing (using H2) or CI/integration testing (using MariaDB4j), it blew up in our production-like setup. (Though, once the problem was understood, it was easy enough to see the wrong value in the MariaDB4j instance; it just didn't blow up the test.) The solution was to build a custom type mapper:

package redacted;

import javax.persistence.AttributeConverter;
import java.net.URI;
import java.net.URISyntaxException;

import static java.lang.String.format;

public class UriConverter implements AttributeConverter<URI, String> {
    @Override
    public String convertToDatabaseColumn(URI attribute) {
        return attribute.toString();
    }

    @Override
    public URI convertToEntityAttribute(String field) {
        try {
            return new URI(field);
        }
        catch (URISyntaxException e) {
            throw new RuntimeException(format("could not convert database field to URI: %s", field));
        }
    }
}

Used as follows:

@Column(nullable = false, columnDefinition = "VARCHAR(255)")
@Convert(converter = UriConverter.class)
private URI consulUri;

As far as Hibernate is involved, it seems it has a bunch of provided type mappers, including for java.net.URL, but not for java.net.URI (which is what we needed here).

---

In my case ,first i've meet a '???' in my website, then i check Mysql's character set which is latin now ,so i change it into utf-8,then i restart my project ,then i got the same error with you , then i found that i forget to change the database's charset and change into utf-8, boom,it worked.

---

I tried almost every steps mentioned here. None worked. Downloaded mariadb. It worked. I know this is not a solution yet this might help somebody to identify the problem quickly or give a temporary solution.

Server version: 10.2.10-MariaDB - MariaDB Server
Protocol version: 10
Server charset: UTF-8 Unicode (utf8)

---

In my case that problem was solved by changing Mysql column encoding to 'binary' (data type will be changed automatically to VARBINARY). Probably I will not be able to filter or search with that column, but I'm no need for that.

---

In my case, Incorrect string value: '\xCC\x88'..., the problem was that an o-umlaut was in its decomposed state. This question-and-answer helped me understand the difference between o¨ and ö. In PHP, the fix for me was to use PHP's Normalizer library. E.g., Normalizer::normalize('o¨', Normalizer::FORM_C).

---

1 - You have to declare in your connection the propertie of enconding UTF8. http://php.net/manual/en/mysqli.set-charset.php.

2 - If you are using mysql commando line to execute a script, you have to use the flag, like: Cmd: C:\wamp64\bin\mysql\mysql5.7.14\bin\mysql.exe -h localhost -u root -P 3306 --default-character-set=utf8 omega_empresa_parametros_336 < C:\wamp64\www\PontoEletronico\PE10002Corporacao\BancoDeDadosModelo\omega_empresa_parametros.sql

참고URL : https://stackoverflow.com/questions/1168036/how-to-fix-incorrect-string-value-errors