mysql 更新一个字符

Question

我正在使用 mysql 并在表“项目”中更新变量 image_url“成功”，没有任何警告。但是，实际上，更新失败了:它在值前面加上一个空格并删除了我给它的值的最后一个字符。

这里是更新:

UPDATE items
    SET image_url = 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg'
    WHERE id=38;

这里是选择:

select * from items\G;

这是输出的一行:

...
image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
...

注意末尾缺少的“g”和开头的额外空格。

我该如何阻止它？

以下是一些可能有帮助的系统信息:

mysql> show variables LIKE '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.5.46                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| version                 | 5.5.46-0ubuntu0.14.04.2 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | i686                    |
| version_compile_os      | debian-linux-gnu        |
+-------------------------+-------------------------+
7 rows in set (0.00 sec)

编辑 1 表说明:

mysql> desc items;

+-------------+---------------+------+-----+---------+----------------+
| Field       | Type          | Null | Key | Default | Extra          |
+-------------+---------------+------+-----+---------+----------------+
...
| image_url   | varchar(255)  | NO   |     | NULL    |                |
...

EDIT 2 检查触发器:

mysql> show triggers \G
Empty set (0.00 sec)

EDIT 3 另一个例子:

我正在从命令行执行所有这些命令。另一个例子:

UPDATE items SET image_url = 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg33333333333333' WHERE id=38;

select * from items\G;

...
image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg3333333333333
...

EDIT 4 检查输入和输出的长度:

mysql> select image_url,length(image_url) from items where id=38\G;
*************************** 1. row ***************************
        image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
length(image_url): 61
1 row in set (0.00 sec)

http://www.lettercount.com/给出 http://ecx.images-amazon.com/images/I/61Dz5t8wjQL.SX522.jpg也是 61 个字符，这是有道理的，因为更新没有改变字符串的长度，只是删除最后一个字符并在开头添加一个空格，

编辑 5 尝试编码:

base64编码:aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw==

mysql> UPDATE items SET image_url = 'aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw==' WHERE id=38;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select image_url,length(image_url) from items where id=38\G;
*************************** 1. row ***************************
        image_url:  aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw=
length(image_url): 84
1 row in set (0.00 sec)

解码:aHR0cDovL2VjeC5pbWFnZXMtYW1hem9uLmNvbS9pbWFnZXMvSS82MUR6NXQ4d2pRTC5fU1g1MjJfLmpwZw=

给出:http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg

EDIT 6 检查 Insert 是否也失败:

mysql> INSERT INTO items (url, image_url) VALUES('http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg', 'http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jpg');
Query OK, 1 row affected, 2 warnings (0.03 sec)

警告是因为我没有给出所有值，其中 NULL:NO values in this insert

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1364 | Field 'created_at' doesn't have a default value |
| Warning | 1364 | Field 'updated_at' doesn't have a default value |
+---------+------+-------------------------------------------------+
2 rows in set (0.00 sec)


mysql> select image_url,length(image_url),url from items where id=39\G;
*************************** 1. row ***************************
        image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
length(image_url): 61
              url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
1 row in set (0.00 sec)

因此，它在插入时也会失败。

EDIT 7 创建表信息

mysql> show create table items\G;
*************************** 1. row ***************************
       Table: items
Create Table: CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  ...
  `image_url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `color` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  ...
  `store` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_items_on_id` (`id`),
  KEY `index_items_on_url` (`url`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

ERROR: 
No query specified

EDIT 8 更多表格和列信息

select * from information_schema.columns where table_name='items' and column_name='image_url'\G;

*************************** 2. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: development_database
              TABLE_NAME: items
             COLUMN_NAME: image_url
        ORDINAL_POSITION: 5
          COLUMN_DEFAULT: NULL
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 255
  CHARACTER_OCTET_LENGTH: 765
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_unicode_ci
             COLUMN_TYPE: varchar(255)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select,insert,update,references
          COLUMN_COMMENT: 
2 rows in set (0.01 sec)

ERROR: 
No query specified

EDIT 9 字符长度读数

mysql> select image_url,length(image_url),char_length(image_url),url from items where id=39\G;
*************************** 1. row ***************************
             image_url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
     length(image_url): 61
char_length(image_url): 61
                   url:  http://ecx.images-amazon.com/images/I/61Dz5t8wjQL._SX522_.jp
1 row in set (0.00 sec)

ERROR: 
No query specified

EDIT 10 显示字符等变量

mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

编辑 11:潜在问题

错误没有出现在users表中，但确实出现在items表中。这是我认为可能导致问题的差异。 (我还没有解决方案，因为项目表有那个 UTF-8 是有原因的:url 可以有一些时髦的字符)

show create table users\G;

ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

show create table items\G;

ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

To be honest I think this should be a Community Answer, as I was a little later on the scene and others had done some important ground work establishing what was and was not a factor in this issue.

This link may be relevant ，因为你的表字符集是 utf8 所以字符串中的最后一个字符可能会倾斜(并且没有正确保存，因此消失)。

EDIT 10 中引用 latin1 或 utf8 字符集排序规则的所有行应该相同，理想情况下应该是 utf8mb4 。我现在冒昧地猜测，在非 true-utf-8 字符排序规则中保存 UTF-8 字符意味着任何字符串的最后一个字符都是不完整的引用，因此不会显示。

因此，要解决您的问题，请运行命令:

ALTER TABLE items CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

---

信息/背景:

utf8mb4 是完整的 UTF-8 字符集，因此将显示可在网址中使用的所有字符。如果数据中有一些晦涩的字符，我建议您将列更改为 BLOB 列 before 然后将其更改为 utf8mb4 列，因为这将保留正确的字符定义作为输入，而不是MySQL 假设的 对已输入的数据。

你不需要 utf8_ 字符集，在 MySQL 中好坏参半，你想要的是 utf8mb4，MySQL 中的标准 UTF8 定义被破坏了，因为它将 4 字节字符保存在 3 字节 block 中，从而损坏保存的字符数据。