Category: MySQL
-
mysql remove auto_increment
show create table 对应字段 `id` bigint(20) NOT NULL AUTO_INCREMENT 现在重新 alter alter table `dw`.`lesson` change id id bigint(20) NOT NULL; 这样就没有 auto_increment 了
-
mysql sql_mode
设置 sql_mode set global sql_mode=”ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”; 查看 sql_mode show variables like ‘sql_mode’; OR SELECT @@sql_mode
-
mysql explicit_defaults_for_timestamp
设置 explicit_defaults_for_timestamp mysql> SET global explicit_defaults_for_timestamp=1;
-
mysqldump ignore table
mysqldump 忽略一些表 –ignore-table=name Do not dump the specified table. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., –ignore-table=database.table 例子 mysqldump -u dw -p dw –ignore-table=dw.raw_tracking –ignore-table=dw.raw_access > dump_without_tracking_access.sql
-
mysqldump 加 where 条件
导出表，时间大于 2017-05-17 mysqldump -u root -p dw tracking –where ‘stm > 2017-05-17’ > dw.tracking.2017-05-17.sql
-
更改 mysql 慢查询时间阈值
默认是 10s ，改为 200ms mysql> show global variables like ‘long_query_time%’; +—————–+———–+ | Variable_name | Value | +—————–+———–+ | long_query_time | 10.000000 | +—————–+———–+ 1 row in set (0.01 sec) mysql> set global long_query_time=0.2; Query OK, 0 rows affected (0.01 sec) mysql> show global variables like ‘long_query_time%’; +—————–+———-+ | Variable_name | Value | +—————–+———-+ | long_query_time…