mysql 5.7导入5.0_升级到MySQL5.7版本需注意的问题

升级到MySQL5.7版本需注意的问题

1)在一个无符号列中插入一个负值创建具有无符号列的表：CREATE TABLE test (id int unsigned);插入负值。以前版本：INSERT INTO test VALUES (-1);

Query OK, 1 row affected, 1 warning (0.01 sec)

MySQL 5.7版本：INSERT INTO test VALUES (-1);

ERROR 1264 (22003): Out of range value for column 'a' at row 1

2)除以零创建测试表：CREATE TABLE test2 (   id int unsigned  );

尝试除以零。先前版本：INSERT INTO test2 VALUES (0/0);

Query OK, 1 row affected (0.01 sec)

MySQL 5.7版本：INSERT INTO test2 VALUES (0/0);

ERROR 1365 (22012): Division by 0

3)在10个字符的列中插入20个字符的字符串创建一个包含10个字符的列的表：CREATE TABLE test3 (  a varchar(10)  );

尝试插入较长的字符串。先前版本：INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');

Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7版本：INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');

ERROR 1406 (22001): Data too long for column 'a' at row 1

4)将非标准零日期插入日期时间列创建具有datetime列的表：CREATE TABLE test3 (  a datetime  );

插入0000-00-00 00:00:00。先前版本：INSERT INTO test3 VALUES ('0000-00-00 00:00:00');

Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7版本：INSERT INTO test3 VALUES ('0000-00-00 00:00:00');

ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

5)使用GROUP BY并选择一个不明确的列发生这种情况时的说明不是一部分GROUP BY，并且没有聚集函数(例如MIN或MAX)施加到其上。先前版本：SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;

+----+------------+-------------+

| id | invoice_id | description |

+----+------------+-------------+

| 1 | 1 | New socks             |

| 3 | 2 | Shoes                 |

| 5 | 3 | Tie                   |

+----+------------+-------------+

3 rows in set (0.00 sec)

MySQL 5.7版本：SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

5)mysql5.7版本SQL_MODE默认情况下，MySQL 5.7包含以下模式：* ONLY_FULL_GROUP_BY

* STRICT_TRANS_TABLES

* NO_ENGINE_SUBSTITUTION

* NO_AUTO_CREATE_USER

该模式STRICT_TRANS_TABLES也变得更加严格，在mysql 5.7中，默认使用的是严格模式，有时候可能带来问题，

比如：mysql> CREATE TABLE `events_t` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `event_date` datetime NOT NULL,

-> `profile_id` int(11) DEFAULT NULL,

-> PRIMARY KEY (`id`),

-> KEY `event_date` (`event_date`),

-> KEY `profile_id` (`profile_id`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1

-> ;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into events_t (profile_id) values (1);

ERROR 1364 (HY000): Field 'event_date' doesn't have a default value

这里日期就必须要有默认值，不能NULL，但：mysql> alter table events_t change event_date event_date datetime NOT NULL default '0000-00-00 00:00:00';

ERROR 1067 (42000): Invalid default value for 'event_date'

mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-00-00 00:00:00';

ERROR 1067 (42000): Invalid default value for 'event_date'

也是不行的，必须要有真实日期默认mysql> alter table events_t change event_date event_date datetime NOT NULL default '2000-01-01 00:00:00';

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into events_t (profile_id) values (1);

Query OK, 1 row affected (0.00 sec)

因此只有日期格式改为timestamp，或者用NULL，或者在insert的时候用now()产生日期。

6)mysql-connector-java.Jar尽可能更新到最新版本，以下为官方推荐对应版本列表

Table 2.1 Summary of Connector/J Versions

Connector/J version

Driver Type

JDBC version

MySQL Server version

Status

5.1

4

3.0, 4.0, 4.1, 4.2

4.1, 5.0, 5.1, 5.5, 5.6, 5.7

Recommended version

5.0

4

3.0

4.1, 5.0

Released version

3.1

4

3.0

4.1, 5.0

Obsolete

3.0

4

3.0

3.x, 4.1

Obsolete

Table 26.2 MySQL Connector Versions and MySQL Server Versions

Connector

Connector version

MySQL Server version

Connector/C

6.1.0 GA

5.6, 5.5, 5.1, 5.0, 4.1

Connector/C++

1.0.5 GA

5.6, 5.5, 5.1

Connector/J

5.1.8

5.6, 5.5, 5.1, 5.0, 4.1

Connector/Net

6.5

5.6, 5.5, 5.1, 5.0

Connector/Net

6.4

5.6, 5.5, 5.1, 5.0

Connector/Net

6.3

5.6, 5.5, 5.1, 5.0

Connector/Net

6.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.1 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

6.0 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

5.2 (No longer supported)

5.6, 5.5, 5.1, 5.0

Connector/Net

1.0 (No longer supported)

5.0, 4.0

Connector/ODBC

5.1

5.6, 5.5, 5.1, 5.0, 4.1.1+

Connector/ODBC

3.51 (Unicode not supported)

5.6, 5.5, 5.1, 5.0, 4.1

Connector/Python

2.0

5.7, 5.6, 5.5

Connector/Python

1.2

5.7, 5.6, 5.5