jpuyy的网志

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 | 0.200000 |
+-----------------+----------+
1 row in set (0.00 sec)