美国人来了, 有面包有自由; 苏俄来了, 有面包无自由; 中共来了, 无面包无自由[email protected]胡适 (思想家) http://program-think.blogspot.com/2014/07/artists-and-ccp.html

MySQL update Error 1292

MySQL 2017-08-28 浏览量: 1952 字数统计: 358 最后更新: 2017-08-28 09:59

Error | 1292 | Truncated incorrect DOUBLE value: 'XXXXXX'

表结构:

CREATE TABLE `test`.`student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `age` INT NOT NULL,
  `sex` INT NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


# 插入数据

INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小张', '20', '1');
INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小赵', '18', '2');
INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小周', '18', '1');
INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小吴', '19', '1');
INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小王', '18', '2');
INSERT INTO `test`.`student` (`name`, `age`, `sex`) VALUES ('小李', '18', '2');

# 

mysql> select * from student;
+----+--------+-----+-----+
| id | name   | age | sex |
+----+--------+-----+-----+
|  1 | 小张   |  20 |   1 |
|  2 | 小赵   |  18 |   2 |
|  3 | 小周   |  18 |   1 |
|  4 | 小吴   |  19 |   1 |
|  5 | 小王   |  18 |   2 |
|  6 | 小李   |  18 |   2 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)


mysql> select name="小张", sex=1 from student;
+---------------+-------+
| name="小张"   | sex=1 |
+---------------+-------+
|             1 |     1 |
|             0 |     0 |
|             0 |     1 |
|             0 |     1 |
|             0 |     0 |
|             0 |     0 |
+---------------+-------+
6 rows in set (0.00 sec)


此时我们想把 sex =1 的name 全换成 小张,并且把 年龄全换成18

开始我是这么写的,

mysql> update student set name='小张' and sex=2 where sex=1;

# 查看一下报错信息

mysql> show errors;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Error | 1292 | Truncated incorrect DOUBLE value: '小张'   |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

当前的版本是:
Server version: 5.7.18,会直接报错,换在之前的版本会给 warning,你需要去查看一下警告是什么

这里面出现了 Double ,很奇怪,name 类型是varchar ,而sex类型是int,以防万一我门查看一下

mysql> show fields from student like "name";
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(45) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> show fields from student like "sex";
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| sex   | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)

于是很明确是 update 写错了,于是查看了一下优先级,明确了 一下先后顺序,发现是这样的

set name=('小张' and sex=2)

所以说执行 ('小张' and sex=2) 的结果是假 也就是0, 如果没有报错而是 单纯警告的话,这个语句的结果应该是这样的

update student set name=0 where sex=1;

其实也可以在这里执行一下查询计划,但是在此处并没有什么用处

mysql> explain update student set name="小张" and sex=1 where sex=1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where
1 row in set (0.00 sec)

最后我们需要改正这个查询语句,所以应该把 and 换掉

mysql> update student set name='小张',sex=2 where sex=1;
Query OK, 3 rows affected (0.13 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select name="小张",sex=2 from student;
+---------------+-------+
| name="小张"   | sex=2 |
+---------------+-------+
|             1 |     1 |
|             0 |     1 |
|             1 |     1 |
|             1 |     1 |
|             0 |     1 |
|             0 |     1 |
+---------------+-------+
6 rows in set (0.00 sec)

error错误代码的查询

其中MySQL发行版里面携带了一个可以查询错误代码的工具 perror,它能提供一些与给定的错误代码相关联的MySQL 及其所在系统的错误信息


下面的是使用说明文档

$ perror --help
perror Ver 2.11, for Linux (x86_64)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Print a description for a system error code or a MySQL error code.
If you want to get the error for a negative error code, you should use
-- before the first error code to tell perror that there was no more options.

Usage: perror [OPTIONS] [ERRORCODE [ERRORCODE...]]
  -?, --help          Displays this help and exits.
  -I, --info          Synonym for --help.
  -s, --silent        Only print the error message.
  -v, --verbose       Print error code and message (default).
                      (Defaults to on; use --skip-verbose to disable.)
  -V, --version       Displays version information and exits.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           TRUE
# 查看一下刚刚update 的错误代码 1292

$ perror 1292
MySQL error code 1292 (ER_TRUNCATED_WRONG_VALUE): Truncated incorrect %-.32s value: '%-.128s'

# 常见的语法错误代码1064
$ perror 1064
MySQL error code 1064 (ER_PARSE_ERROR): %s near '%-.80s' at line %d


$ perror 2
OS error code   2:  No such file or directory

小蜗牛 说:
Freedom is the source from which all meaning and all values spring .


文章版权归 原文作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权| 转载必须包含本声明,并以超链接形式注明原文作者和本文原始地址: https://www.tougetu.com/2017/08/mysql-update-error.html

还不快抢沙发

添加新评论

代码 Pastebin Gist 加粗 删除线 斜体 链接 签到