Home >Database >Mysql Tutorial >辛星浅谈mysql中的元数据_MySQL

辛星浅谈mysql中的元数据_MySQL

WBOY
WBOYOriginal
2016-06-01 13:02:08928browse

首先解释一下什么是元数据,所谓元数据,就是表示数据的数据,这些数据五花八门,总之,只要不是我们存储到数据库里的数据,大多都可以理解为元数据。那么我们如何来获取这些元数据呢?

总的来说,有三种思路,第一种,各种show,第二种,各种select,第三种,是mysql的命令行下的命令,不是sql语句。

我们首先看第一种,这里我列举一下大家比较熟悉的show语句的用法,其实咱们经常用show来查看信息,比如:

show databases;
show tales;
show create table 表名;
show index from 表名;
....等等

第二种就是通过select来从mysql数据库或者information_schema数据库中提取相关信息,比如可以从tables这个表里面的free_data字段来查看数据碎片的相关信息,这里我上一篇博文已经介绍过了,这里就不废话了。

第三种就是通过mysql自带的一些命令来查看,这个需要咱们的用户名和密码的,下面是我查看相应元数据的代码,希望能帮到您:

Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。

C:\Users\Administrator>mysqlshow -u root -proot
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| xinxing            |
+--------------------+

C:\Users\Administrator>mysqlshow -u root -proot xinxing
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: xinxing
+--------+
| Tables |
+--------+
| xin    |
+--------+

C:\Users\Administrator>mysqlshow -u root -proot --status xinxing
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: xinxing
+------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-------
----+----------------+---------------------+---------------------+---------------------+-----------------+----------+---
-------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_f
ree | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Cr
eate_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-------
----+----------------+---------------------+---------------------+---------------------+-----------------+----------+---
-------------+---------+
| xin  | MyISAM | 10      | Fixed      | 3    | 121            | 363         | 34058472181989375 | 1024         | 0
    |                | 2014-08-25 13:19:02 | 2014-08-25 13:41:25 | 2014-08-25 13:41:25 | utf8_general_ci |          |
             |         |
+------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-------
----+----------------+---------------------+---------------------+---------------------+-----------------+----------+---
-------------+---------+

C:\Users\Administrator>

下面是我用mysqldump来查看表结构的一些操作,希望对您有用:

C:\Users\Administrator>mysqldump -u root -proot --no-data xinxing xin
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 5.7.3-m13, for Win64 (x86_64)
--
-- Host: localhost    Database: xinxing
-- ------------------------------------------------------
-- Server version       5.7.3-m13

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `xin`
--

DROP TABLE IF EXISTS `xin`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `xin` (
  `c` char(40) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2014-08-25 20:49:08

辛星,期待您的关注。。

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn