Shulou(Shulou.com)06/01 Report--

In fact, this error has something to do with implicit conversion.

It is mainly due to the problem that it cannot be converted to the desired format during the conversion.

For example, it is impossible to convert the character 1p to int (number).

The MYSQL error is simulated as follows:

Myql > show variables like 'sql_mode%'

+-+

| | Variable_name | Value |

+-+

| | sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |

+-+

1 row in set (0.01 sec)

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > create table jjjkk (id varchar (20), name varchar (20))

Query OK, 0 rows affected (0.03 sec)

Mysql > insert into jjjkk values ('1century recording gaopeng')

Query OK, 1 row affected (0.00 sec)

Mysql > update jjjkk set name='gaop' where id=1

Query OK, 1 row affected (0.05sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > update jjjkk set name='gaop' where id=1

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Mysql > insert into jjjkk values ('1pause recording gaopeng')

Query OK, 1 row affected (0.01sec)

Mysql > commit

Query OK, 0 rows affected (0.00 sec)

Mysql > update jjjkk set name='gaop' where id=1

ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1p'

There is implicit conversion in MYSQL. ORACLE usually gives signs such as to_char in the execution plan.

But there is no MYSQL implementation plan.

As listed above, although'1' can be converted, there is no error at this time. But the newly inserted'1p 'cannot be converted.

This is also a problem in ORACLE. So report the error.

ERROR 1292 (22007): Truncated incorrect DOUBLE value: '1p'

Of course I'm a strict SQL_MODE, otherwise I'm WARINGS.

You can try the conversion as follows:

SELECT CONVERT ('1century Magazine signed)

SELECT CONVERT ('1paired Magazine Signed)

Then demonstrate the error report of ORACLE:

SQL > create table testmmm (id varchar2 (20), name varchar2 (20))

Table created

SQL > insert into testmmm values ('1century recording gaopeng')

1 row inserted

SQL > commit

Commit complete

SQL > select * from testmmm where id=1

ID NAME

--

1 gaopeng

SQL > insert into testmmm values ('1pause recording gaopeng')

1 row inserted

SQL > commit

Commit complete

SQL > select * from testmmm where id=1

Select * from testmmm where id=1

ORA-01722: invalid number

If you simulate

SQL > select to_number ('1p') from dual

Select to_number ('1p') from dual

ORA-01722: invalid number

The same situation.

Let's take a look at the implicit conversion information in the execution plan:

Predicate Information (identified by operation id):

1-filter (TO_NUMBER ("ID") = 1)

Only this record reports the same error as MYSQL and ORACLE

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.