MySQL中快速找出无显式主键的表

最新推荐文章于 2021-03-23 22:56:15 发布
最新推荐文章于 2021-03-23 22:56:15 发布
阅读量346 收藏
点赞数
文章标签： mysql 数据库 sql java 面试
原文链接：https://ke.qq.com/course/1346083?saleToken=1989182\x26amp;from=pclink
版权

大家好，我是知数堂SQL 优化班老师 网名：骑龟的兔子

版本 ：Server version: 8.0.22 MySQL Community Server - GPL

目标：想要查找没有显示主键的表

第一步：查询所有用户表 

SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE';


+--------------+------------------+
| TABLE_SCHEMA | TABLE_NAME       |
+--------------+------------------+
| employees    | departments      |
| employees    | dept2            |
| employees    | dept_emp         |
| employees    | dept_emp2        |
| employees    | dept_emp3        |
| employees    | dept_manager     |
| employees    | emp1             |
| employees    | emp2             |
| employees    | emp3             |
| employees    | employees        |
| employees    | salaries         |
| employees    | salaries2        |
| employees    | salaries3        |
| employees    | salaries4_up_20w |
| employees    | t0522            |
| employees    | t1               |
| employees    | t11              |
| employees    | t11_1            |
| employees    | t12              |
| employees    | t4               |
| employees    | t_g1             |
| employees    | t_group          |
| employees    | t_group2         |
| employees    | t_group21        |
| employees    | t_group3         |
| employees    | t_group4         |
| employees    | t_group5         |
| employees    | t_group6         |
| employees    | t_order          |
| employees    | t_time           |
| employees    | test1            |
| employees    | titles           |
| employees    | txt_t1           |
| test         | clone_progress   |
| test         | customer         |
| test         | district         |
| test         | history          |
| test         | item             |
| test         | new_orders       |
| test         | order_line       |
| test         | orders           |
| test         | stock            |
| test         | t1               |
| test         | t2               |
| test         | t3               |
| test         | tb_task          |
| test         | tb_task_order    |
| test         | warehouse        |
+--------------+------------------+
48 rows in set (0.00 sec)

第二步 ： 查找所有包含显示主键的表 



SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'


+--------------+---------------+
| TABLE_SCHEMA | TABLE_NAME    |
+--------------+---------------+
| employees    | departments   |
| employees    | dept_emp      |
| employees    | dept_manager  |
| employees    | emp1          |
| employees    | emp3          |
| employees    | employees     |
| employees    | salaries      |
| employees    | salaries3     |
| employees    | t1            |
| employees    | t11           |
| employees    | t12           |
| employees    | t_group2      |
| employees    | t_group21     |
| employees    | t_group6      |
| employees    | t_time        |
| employees    | titles        |
| test         | customer      |
| test         | district      |
| test         | item          |
| test         | new_orders    |
| test         | order_line    |
| test         | orders        |
| test         | stock         |
| test         | t1            |
| test         | t3            |
| test         | tb_task       |
| test         | tb_task_order |
| test         | warehouse     |
+--------------+---------------+
28 rows in set (0.00 sec)

第三步：两个结果集进行JOIN ，找到差异

SELECT
a.*, b.*
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE'
) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
b.TABLE_NAME IS NULL;


Empty set (0.00 sec)

同样的思路我们在

Server version:   5.7.28-log MySQL Community Server (GPL)

SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE';
+--------------+-------------------------+
| TABLE_SCHEMA | TABLE_NAME              |
+--------------+-------------------------+
| employees    | departments             |
| employees    | dept2                   |
| employees    | dept_emp                |
| employees    | dept_emp2               |
| employees    | dept_emp3               |
| employees    | dept_manager            |
| employees    | emp1                    |
| employees    | emp2                    |
| employees    | emp3                    |
| employees    | employees               |
| employees    | salaries                |
| employees    | salaries2               |
| employees    | salaries3               |
| employees    | salaries4_up_20w        |
| employees    | salaries5               |
| employees    | salaries6               |
| employees    | t1                      |
| employees    | t11                     |
| employees    | t11_1                   |
| employees    | t4                      |
| employees    | t_g1                    |
| employees    | t_group                 |
| employees    | t_group2                |
| employees    | t_group3                |
| employees    | t_group4                |
| employees    | t_group5                |
| employees    | t_order                 |
| employees    | t_time                  |
| employees    | test1                   |
| employees    | titles                  |
| employees    | tmp1                    |
| employees    | txt_t1                  |
| test         | clone_progress          |
| test         | disk_free               |
| test         | l                       |
| test         | l2                      |
| test         | t1                      |
| test         | t2                      |
| test         | t_msg_history_2017_06_1 |
| test         | tb_task                 |
| test         | tb_task_order           |
| test         | ts_lock                 |
+--------------+-------------------------+
42 rows in set (0.00 sec)


SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
+--------------+-------------------------+
| TABLE_SCHEMA | TABLE_NAME              |
+--------------+-------------------------+
| employees    | departments             |
| employees    | dept_emp                |
| employees    | dept_manager            |
| employees    | emp1                    |
| employees    | emp3                    |
| employees    | employees               |
| employees    | salaries                |
| employees    | salaries3               |
| employees    | salaries5               |
| employees    | salaries6               |
| employees    | t1                      |
| employees    | t11                     |
| employees    | t_time                  |
| employees    | titles                  |
| test         | disk_free               |
| test         | l                       |
| test         | l2                      |
| test         | t_msg_history_2017_06_1 |
| test         | tb_task                 |
| test         | tb_task_order           |
| test         | ts_lock                 |
+--------------+-------------------------+
21 rows in set (0.00 sec)


SELECT
a.*, b.*
FROM
(
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLES t
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND
TABLE_TYPE = 'BASE TABLE'
) AS a
LEFT JOIN (
SELECT
TABLE_SCHEMA,
TABLE_NAME
FROM
information_schema.TABLE_CONSTRAINTS c
WHERE
TABLE_SCHEMA NOT IN (
'mysql',
'sys',
'information_schema',
'performance_schema'
) AND CONSTRAINT_TYPE = 'PRIMARY KEY'
) AS b USING (TABLE_SCHEMA, TABLE_NAME)
WHERE
b.TABLE_NAME IS NULL;


+--------------+------------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME       | TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+--------------+------------+
| employees    | dept2            | NULL         | NULL       |
| employees    | dept_emp2        | NULL         | NULL       |
| employees    | dept_emp3        | NULL         | NULL       |
| employees    | emp2             | NULL         | NULL       |
| employees    | salaries2        | NULL         | NULL       |
| employees    | salaries4_up_20w | NULL         | NULL       |
| employees    | t11_1            | NULL         | NULL       |
| employees    | t4               | NULL         | NULL       |
| employees    | t_g1             | NULL         | NULL       |
| employees    | t_group          | NULL         | NULL       |
| employees    | t_group2         | NULL         | NULL       |
| employees    | t_group3         | NULL         | NULL       |
| employees    | t_group4         | NULL         | NULL       |
| employees    | t_group5         | NULL         | NULL       |
| employees    | t_order          | NULL         | NULL       |
| employees    | test1            | NULL         | NULL       |
| employees    | tmp1             | NULL         | NULL       |
| employees    | txt_t1           | NULL         | NULL       |
| test         | clone_progress   | NULL         | NULL       |
| test         | t1               | NULL         | NULL       |
| test         | t2               | NULL         | NULL       |
+--------------+------------------+--------------+------------+
21 rows in set (0.01 sec)

可以看到在5.7中就可以得到我们想要的答案，但8.0.22中就不行。

我们现在开始分析其原因，首先我们来比较下不同版本之间的SQL的执行计划。

5.7

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_frm_only; Scanned all databases                                          |
|  1 | SIMPLE      | c     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using where; Open_full_table; Scanned all databases; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
root@mysql3357.sock>[employees]>show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`t`.`TABLE_NAME` AS `TABLE_NAME`,`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`c`.`TABLE_NAME` AS `TABLE_NAME` 
from `information_schema`.`tables` `t` 
left join (`information_schema`.`table_constraints` `c`) on(((`c`.`TABLE_NAME` = `t`.`TABLE_NAME`) and (`c`.`TABLE_SCHEMA` = `t`.`TABLE_SCHEMA`) 
and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`c`.`CONSTRAINT_TYPE` = 'PRIMARY KEY'))) 
where (isnull(`c`.`TABLE_NAME`) and (`t`.`TABLE_SCHEMA` not in ('mysql','sys','information_schema','performance_schema')) and (`t`.`TABLE_TYPE` = 'BASE TABLE'))
1 row in set (0.00 sec)

我们可以看到解析到的SQL 中包含 isnull(`c`.`TABLE_NAME`) 

8.0



+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
| id | select_type       | table      | partitions | type   | possible_keys         | key         | key_len | ref                           | rows | filtered | Extra                                   |
+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY               | name        | 194     | NULL                          |    1 |   100.00 | Using index                             |
|  1 | PRIMARY           | sch        | NULL       | ref    | PRIMARY,catalog_id    | catalog_id  | 8       | mysql.cat.id                  |    6 |   100.00 | Using where; Using index                |
|  1 | PRIMARY           | tbl        | NULL       | ref    | schema_id,type,type_2 | schema_id   | 8       | mysql.sch.id                  |   47 |    67.78 | Using where                             |
|  1 | PRIMARY           | col        | NULL       | eq_ref | PRIMARY               | PRIMARY     | 8       | mysql.tbl.collation_id        |    1 |   100.00 | Using index                             |
|  1 | PRIMARY           | ts         | NULL       | eq_ref | PRIMARY               | PRIMARY     | 8       | mysql.tbl.tablespace_id       |    1 |   100.00 | Using index                             |
|  1 | PRIMARY           | stat       | NULL       | eq_ref | PRIMARY               | PRIMARY     | 388     | mysql.sch.name,mysql.tbl.name |    1 |   100.00 | Using where; Using index                |
|  1 | PRIMARY           | cat        | NULL       | index  | PRIMARY               | name        | 194     | NULL                          |    1 |   100.00 | Using index                             |
|  1 | PRIMARY           | sch        | NULL       | eq_ref | PRIMARY,catalog_id    | catalog_id  | 202     | mysql.cat.id,func             |    1 |   100.00 | Using where; Using index                |
|  1 | PRIMARY           | tbl        | NULL       | eq_ref | schema_id             | schema_id   | 202     | mysql.sch.id,func             |    1 |   100.00 | Using where; Rematerialize (<derived6>) |
|  1 | PRIMARY           | <derived6> | NULL       | ref    | <auto_key0>           | <auto_key0> | 35      | const                         |    1 |   100.00 | NULL                                    |
|  6 | DEPENDENT DERIVED | idx        | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    1 |    40.00 | Using where                             |
|  7 | DEPENDENT UNION   | fk         | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    2 |   100.00 | Using index                             |
|  8 | DEPENDENT UNION   | cc         | NULL       | ref    | table_id              | table_id    | 8       | mysql.tbl.id                  |    1 |   100.00 | NULL                                    |
+----+-------------------+------------+------------+--------+-----------------------+-------------+---------+-------------------------------+------+----------+-----------------------------------------+
13 rows in set, 5 warnings (0.00 sec)


Message: /* select#1 */ select (`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME`,(`mysql`.`sch`.`name` collate utf8_tolower_ci) AS `TABLE_SCHEMA`
,(`mysql`.`tbl`.`name` collate utf8_tolower_ci) AS `TABLE_NAME` 
from `mysql`.`tables` `tbl` 
join `mysql`.`schemata` `sch` 
join `mysql`.`catalogs` `cat` 
left join `mysql`.`collations` `col` on((`col`.`id` = `tbl`.`collation_id`)) 
left join `mysql`.`tablespaces` `ts` on((`ts`.`id` = `tbl`.`tablespace_id`)) 
left join `mysql`.`table_stats` `stat` on(((`tbl`.`name` = `stat`.`table_name`) and (`sch`.`name` = `stat`.`schema_name`))) 
left join (`mysql`.`tables` `tbl` 
join `mysql`.`schemata` `sch` 
join `mysql`.`catalogs` `cat` 
join lateral (/* select#6 */ select `mysql`.`idx`.`name` AS `CONSTRAINT_NAME`,if((`mysql`.`idx`.`type` = 'PRIMARY'),'PRIMARY KEY',`mysql`.`idx`.`type`) AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED` 
from `mysql`.`indexes` `idx` 
where ((`mysql`.`idx`.`table_id` = `mysql`.`tbl`.`id`) and (`mysql`.`idx`.`type` in ('PRIMARY','UNIQUE')) and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`,`mysql`.`idx`.`hidden`))) 
union all /* select#7 */ select (`mysql`.`fk`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_NAME`,'FOREIGN KEY' AS `CONSTRAINT_TYPE`,'YES' AS `ENFORCED`
from `mysql`.`foreign_keys` `fk` where (`mysql`.`fk`.`table_id` = `mysql`.`tbl`.`id`) 
union all /* select#8 */ select `mysql`.`cc`.`name` AS `CONSTRAINT_NAME`,'CHECK' AS `CONSTRAINT_TYPE`,`mysql`.`cc`.`enforced` AS `ENFORCED` 
from `mysql`.`check_constraints` `cc` 
where (`mysql`.`cc`.`table_id` = `mysql`.`tbl`.`id`)) `constraints`) on(((`mysql`.`tbl`.`schema_id` = `mysql`.`sch`.`id`) 
and (`mysql`.`sch`.`catalog_id` = `mysql`.`cat`.`id`) and (`constraints`.`CONSTRAINT_TYPE` = 'PRIMARY KEY') 
and ((`mysql`.`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) 
and ((`sch`.`name` collate utf8_tolower_ci) = (`mysql`.`sch`.`name` collate utf8_tolower_ci)) 
and ((`tbl`.`name` collate utf8_tolower_ci) = (`mysql`.`tbl`.`name` collate utf8_tolower_ci)) and (0 <> can_access_table(`mysql`.`sch`.`name`,`mysql`.`tbl`.`name`)) 
and (0 <> is_visible_dd_object(`mysql`.`tbl`.`hidden`)))) where ((`tbl`.`schema_id` = `sch`.`id`) and (`sch`.`catalog_id` = `cat`.`id`) and (`tbl`.`type` = 'BASE TABLE') 
and ((`mysql`.`tbl`.`name` collate utf8_tolower_ci) is null) and ((`sch`.`name` collate utf8_tolower_ci) not in ('mysql','sys','information_schema','performance_schema')) 
and (0 <> can_access_table(`sch`.`name`,`tbl`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`)))
5 rows in set (0.00 sec)

从解析到的SQL中我们没有发现 TABLE_NAME IS NULL;

相关的内容！说明在解析的过程中由于优化器的问题 解析错误了 ！

我们把视图合并功能先关下看看 

set session optimizer_switch='derived_merge=off'


Message: /* select#1 */ select `a`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`a`.`TABLE_NAME` AS `TABLE_NAME`,`b`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`b`.`TABLE_NAME` AS `TABLE_NAME` 
from (/* select#2 */ select `information_schema`.`t`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`t`.`TABLE_NAME` AS `TABLE_NAME` from `information_schema`.`TABLES` `t`) `a` 
left join (/* select#3 */ select `information_schema`.`c`.`TABLE_SCHEMA` AS `TABLE_SCHEMA`,`information_schema`.`c`.`TABLE_NAME` AS `TABLE_NAME` 
from `information_schema`.`TABLE_CONSTRAINTS` `c`) `b` on(((`b`.`TABLE_NAME` = `a`.`TABLE_NAME`) and (`b`.`TABLE_SCHEMA` = `a`.`TABLE_SCHEMA`))) 
where (`b`.`TABLE_NAME` is null)
5 rows in set (0.00 sec)


+--------------+------------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME       | TABLE_SCHEMA | TABLE_NAME |
+--------------+------------------+--------------+------------+
| employees    | dept2            | NULL         | NULL       |
| employees    | dept_emp2        | NULL         | NULL       |
| employees    | dept_emp3        | NULL         | NULL       |
| employees    | emp2             | NULL         | NULL       |
| employees    | salaries2        | NULL         | NULL       |
| employees    | salaries4_up_20w | NULL         | NULL       |
| employees    | t0522            | NULL         | NULL       |
| employees    | t11_1            | NULL         | NULL       |
| employees    | t4               | NULL         | NULL       |
| employees    | t_g1             | NULL         | NULL       |
| employees    | t_group          | NULL         | NULL       |
| employees    | t_group3         | NULL         | NULL       |
| employees    | t_group4         | NULL         | NULL       |
| employees    | t_group5         | NULL         | NULL       |
| employees    | t_order          | NULL         | NULL       |
| employees    | test1            | NULL         | NULL       |
| employees    | txt_t1           | NULL         | NULL       |
| test         | clone_progress   | NULL         | NULL       |
| test         | history          | NULL         | NULL       |
| test         | t2               | NULL         | NULL       |
+--------------+------------------+--------------+------------+
20 rows in set (0.00 sec)

结果发现 TABLE_NAME IS NULL;

又出现了！！最终发现是视图合并搞的鬼 ！视图合并功能是5.7添加的，非常好，但是有时候 就会出现一些问题，但是掌握解决问题的方法和思路，就会从容应对！

我的新一轮的SQL 优化课 即将在春节后开课 

我是知数堂SQL 优化班老师~ ^^

如有关于SQL优化方面疑问和一起交流的请加 并且 @兔子@知数堂SQL优化

高性能MySQL,SQL优化群 有叶金荣，吴炳锡 两位大神坐镇 ：579036588

欢迎加入 知数堂大家庭。

我的微信公众号：SQL开发与优化(sqlturning)

扫码直达宝藏课程

老叶茶馆_
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql快速获得库中无主键的表实例代码
09-09
本篇文章将深入探讨如何在MySQL中快速找到没有设置主键的表，以及无主键表的含义和后果。 首先，我们可以通过SQL查询来检查数据库中特定表的主键信息。以下是一个示例代码： ```sql -- 查看表主键信息 SELECT t....
MySQL8.0中消失又回来的磁盘临时表
老叶茶馆
10-02 881
*GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。1.引子2.发现问题3.破题4.总结1.引子首先，我是一个标题党^_^，写这样的标题就是为了吸引你们来看的。标题中说的磁盘临时表消失，并不是真的消失了，而是在MySQL默认配置下，从8.0.15到8.0.27的版本都不会产生磁盘临时表(8.0.15之前的版本没有做个测试，就不妄下结论了)，在8.0.28及之后的版本中，...
Percona XtraDB Cluster Strict Mode(PXC 5.7)
乐沙弥的世界
04-09 2262
在Percona XtraDB Cluster集群架构中，为了避免多主节点导致的数据异常，或者说一些不被支持的特性引发的数据不一致的情形，PXC集群可以通过配置pxc_strict_mode这个变量来实现。该变量的设置影响还是蛮大的。下文针对这个参数的不同设置进行描述，以及列出相关的具体影响。 一、PXC严格模式描述 PXC严格模式旨在避免在Percona XtraDB群集中使用实验性和不...
mysql utf8 czech ci_MySQL字符集小结
weixin_35823067的博客
02-27 511
备注:测试数据库版本为MySQL 8.0这个blog我们来聊聊MySQL的字符集前言:1.字符集和排序规则说实话我对这两个概念比较模糊，其实可以简单的理解:字符集(character set)：定义了字符以及字符的编码。排序规则(collation)：定义了字符的比较规则。一个字符集对应至少一种排序规则(一般是1对多)两个不同的字符集不能有相同的排序规则每个字符集都有默认的排序规则2.MySQL字...
2020-10-10
CarloPan的博客
10-10 321
CREATE TABLE `table1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `field1` text COLLATE utf8_unicode_ci NOT NULL COMMENT '字段1', `field2` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '字段2', PRIMARY KEY (`id`) ) E.
[SQL]postgreSQL中如何查找无主键的sql语句
Perry in IT World
11-11 1763
查找postgreSQL数据库中，查找无主键的表，可以通下面语句查找： select * from pg_tables where hasindexes is false and schemaname ='public'
快速理解MySQL中主键与外键的实例教程
09-10
当我们试图删除 `dage` 表中的一条记录时，如 `delete from dage where id=1`，如果这条记录在 `xiaodi` 表中有对应的外键引用，MySQL会抛出错误，因为违反了外键约束。同样，如果我们尝试在没有先创建相应的大哥（`...
MySQL 创建主键,外键和复合主键的语句
09-11
本文将详细介绍如何在MySQL中创建主键、外键以及复合主键。 1. **创建主键** 主键是数据库表中一个或一组字段，其值在全表中具有唯一性，并且不能为空。它用于唯一标识表中的每一行记录。在MySQL中，创建主键的...
SQL中几个快速定位语句
princelintb的专栏
06-04 2388
1, 查找表或SP相互yiny的SP SELECT DISTINCT OBJECT_NAME(referencing_id) AS sp_name FROM sys.sql_expression_dependencies (NOLOCK) WHERE referenced_entity_name = '查找表或SP' ORDER BY sp_name
如何定位 问题SQL的【一般套路】
12-27 4941
拿到了一个运行了一段时间的mysql数据库, 该如何进行系统的分析, 尽快的定位问题SQL并解决呢? 一 首先,通过show status命令了解一下当前数据库各种SQL执行的频率.                                              格式：mysql> show [session|global] status;   其中：ses
查询含有clob字段表&查询没有主键表的sql语句
qi20088的专栏
03-07 267
---------------------查询含有clob字段表的sql语句 select * from user_tab_cols where data_type in ('BLOB','CLOB') -------------快捷查询表空间 SELECT 'create tablespace '||tablespace_name ||' DATAFILE ''d:\data\'|...
mysql没有mysqlid_mysql-字段'id'没有默认值？
weixin_33583401的博客
01-19 1925
mysql-字段'id'没有默认值？我是这个SQL的新手。 我已经在更大的程序中看到了类似的问题，目前还无法理解。 我正在建立一个数据库，用于在我的首页中使用的纸牌游戏。我在Windows上使用MySQL Workbench。 我得到的错误是：错误代码：1364。字段“ id”没有默认值CREATE TABLE card_games(nafnleiks varchar(50),leiklysing...
mysql 查看所有没有主键的表
Quincylk的专栏
02-06 2224
SELECT #CONCAT("truncate table ",table_name,";") table_name FROM information_schema. TABLES WHERE table_schema = 'test' AND TABLE_NAME NOT IN ( SELECT table_name FROM ...
mysql表查看主键_mysql 查看所有没有主键的表
weixin_29802461的博客
01-18 1566
SELECT#CONCAT("truncate table ",table_name,";")table_nameFROMinformation_schema. TABLESWHEREtable_schema = ‘scdm‘AND TABLE_NAME NOT IN (SELECTtable_nameFROMinformation_schema.table_constraints tJOIN i...
2017年mysql安装过程_实例详解2017最新版windows安装mysql教程
weixin_39872893的博客
01-30 185
本文主要介绍了2017最新版windows安装mysql教程，本文图文并茂给大家介绍的非常详细,需要的朋友可以参考下，希望能帮助大家更好安装mysql。1.首先在mysql官网下载最新版mysql, 附上链接点击打开链接,根据你的系统型号选择对应的包下载,大约300多兆,版本号为5.7.19下载完之后,解压缩,是一个标准的mysql文件,如图2.第二步,配置系统环境变量,桌面→我的电脑→右键"属性...
mysql数据库id不设置主键处理id自增
u014414110的博客
03-04 5480
一、 解决办法： 1.在数据库中设置自动递增 2.每次去获取数据库的id的最大值 在插入的时候+1 首先：写一个查询这张表id的方法 select id from requires order by id desc limit 1 写一个方法： public integer findId(){ String sql=select id from requires order by id d...
mysql存储字典,详解MySQL-8.0数据字典
weixin_39974030的博客
03-23 1801
1. 引言数据字典(Data Dictionary)中存储了诸多数据库的元数据信息如图1所示，包括基本Database, table, index, column, function, trigger, procedure，privilege等；以及与存储引擎相关的元数据，如InnoDB的tablespace, table_id, index_id等。MySQL-8.0在数据字典上进行了诸多优化，...
mysql 找不到id_mysql查找不存在的id_MySQL
weixin_31049801的博客
01-18 522
最近在群里有人问到怎样才能将mysql表中 查找不存在的id(id自增，或者连续都可以)第一种方法：select bewin_id,a from(select bewin_id,1 as a from (select bewin_id from c_userinfo_his order by bewin_id asc) t where not exists (select 1 from c_use...
mysql 隐藏主键_MySQL中有没有一种方法可以隐式地为表创建主键？
weixin_32916125的博客
01-19 863
In MySQL, when CREATE TABLE, is there a way for MySQL to implicitly create a column (i.e. a column not explicitly declared in CREATE TABLE command) as the primary key of the table?Thanks.解决方案No, the P...
mysql数据库中表怎么自己创建主键
最新发布
05-31
的社会公正性得到保障。此外，应该加强公众和利益相关者的参与和监督，以确保工程项目的社会公正性得到有效保障。 四、结论 工程伦在MySQL中，可以通过以下方式自己创建主键： 1. 在创建表时，使用PRIMARY KEY关键字来指理是工程学科中的一个重要分支，涉及到工程实践中的许多问题，例如质定主键。例如，下面的语句创建一个名为"students"的表，其中id列被指定为主量、安全、环境、社会公正等。为了解决工程伦理的问题，需要加强对键： ``` CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), age INT ); ``` 2.工程师的教育和培训，加强对工程创建表后，使用ALTER TABLE语句来添加主键。例如，下面的语句在已经存在的名为"students"的表中，将id列添加为主键： ``` ALTER TABLE students ADD PRIMARY KEY (id); ``` 需要注意的是，一个表只能有一个主键，主键的值不能为NULL，且主键的值必须唯一。如果不遵守这些规则，就无法创建或更新主键。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值