mysql之查找所有数据库中没有主键的表问题

1

2

3

4

5

6

7

select table_schema,table_name from information_schema.tables

where (table_schema,table_name) not in(

    select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'   

)

and table_schema not in (

    'sys','mysql','information_schema','performance_schema' --排除系统库

);

1

2

3

4

5

6

7

8

9

10

11

create table users

(

    name      varchar(50)                         null,

    salt      char(4)                             null comment '盐',

    password  varchar(255)                        null comment '密码',

    create_at timestamp default CURRENT_TIMESTAMP null comment '创建时间',

    update_at timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',

    tid       int unsigned auto_increment

        primary key

)

    charset = utf8;

1

alter table  users modify tid int not null;

1

alter table  users drop primary key;

1

alter table  users change tid id int unsigned auto_increment primary key;