laravel 无效的日期时间格式：1366 字符串值不正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48270374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid datetime format: 1366 Incorrect string value
提问by omixam
I'm getting this error:
我收到此错误：
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xBD Inch...' for column 'column-name' at row 1
SQLSTATE[22007]：无效的日期时间格式：1366 字符串值不正确：'\xBD Inch...' 列 'column-name' 在第 1 行
My database, table, and column have the format utf8mb4_unicode_cialso column-name is type textand NULL.
我的数据库、表和列的格式为utf8mb4_unicode_ci，列名的类型为text和NULL。
This is the value of the column-name
这是列名的值
[column-name] => Some text before 11 ? and other text after, and after.
[列名] => 11 之前的一些文字？和其他文字之后，之后。
However I wait that laravel adds quotes to column's values, because the values are separated by commas (,). It should be as follow:
但是我等待 laravel 为列的值添加引号，因为这些值用逗号 (,) 分隔。应该如下所示：
[column-name] => 'Some text before 11 ? and other text after, and after.'
[column-name] => '11 之前的一些文字？和其他文字之后，之后。
See below the Schema
请参阅下面的架构
Schema::create('mws_orders', function (Blueprint $table) {
$table->string('custom-id');
$table->string('name');
$table->string('description')->nullable();
$table->string('comment')->nullable();
$table->integer('count')->nullable();
$table->text('column-name')->nullable();
$table->timestamps();
$table->primary('custom-id');
});
I have been looking for on google but not any solution, yet.
我一直在谷歌上寻找，但还没有任何解决方案。
Anyone has an idea how to solve this issue?
任何人都知道如何解决这个问题？
I'm using Laravel 5.5 and MariaDB 10.2.11.
我使用的是 Laravel 5.5 和 MariaDB 10.2.11。
采纳答案by omixam
I solved it, encoding to uft-8 all string columns that generated this error before insert. For example, the column that generated the error was column-name, I encoded as show bellow. Also I found other column with the same error, I used this solution, too.
我解决了它，将所有在插入之前生成此错误的字符串列编码为 uft-8。例如，产生错误的列是column-name，我编码为如下所示。我还发现其他列有相同的错误，我也使用了这个解决方案。
$data [
//key=>values
];
$myModel = new MyModel();
$data['column-name'] = DB::connection()->getPdo()->quote(utf8_encode($data['column-name']));
$myModel->insert($data);
回答by Zhiyong Li
I ran into similar problems with Laravel 5.5 and MariaDB 10.2. When storing some user input t into a varchar column, an exception:
我在 Laravel 5.5 和 MariaDB 10.2 上遇到了类似的问题。将一些用户输入 t 存储到 varchar 列时，出现异常：
SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xE2\x80\x86y\xE2\x80...' for column 'comment' at row 1
SQLSTATE[22007]：无效的日期时间格式：1366 不正确的字符串值：'\xE2\x80\x86y\xE2\x80...' 列 'comment' 在第 1 行
will be thrown.
将被抛出。
Since this only happened on the stage server but not on local dev server, I compared the collation and charset of underlining table, it turns out database and table on stage server use lartin1 while local dev server uses utf8mb64.
由于这仅发生在 stage server 而不是在本地 dev server 上，我比较了下划线表的排序规则和字符集，结果 stage server 上的数据库和 table 使用 lartin1，而本地 dev server 使用 utf8mb64。
The problem was solved by changing database and table collation and char set to utf8mb64 and utf864mb_unicode_ci.
通过将数据库和表的排序规则和字符集更改为 utf8mb64 和 utf864mb_unicode_ci 解决了该问题。
ALTER DATABASE <db_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
For anyone who runs into this problem, please check collation and char set of your database and table. Chances are Laravel app is encoding with utf8 while databases uses something else.
对于遇到此问题的任何人，请检查您的数据库和表的排序规则和字符集。很有可能 Laravel 应用使用 utf8 编码，而数据库使用其他编码。
回答by Rick James
BDis the latin1 (and several others) encoding for ?(one-half). The error message talks about storing that in a datetime. So, it sounds like there are at least two errors --
BD是?（二分之一）的 latin1（和其他几个）编码。错误消息谈到将其存储在日期时间中。所以，听起来至少有两个错误——
- mismatch of
CHARACTER SETs - poorly formulated query
- 不匹配
CHARACTER SETs - 不恰当的查询
You show us something about the CREATE TABLE, but why would "inches" be involved in that?
你向我们展示了一些关于CREATE TABLE，但为什么“英寸”会参与其中？
