将show命令结果集插入到表中

Question

mysql
我有一个表：
CREATE TABLE `mysql_monitor` (
  `id` int(11) NOT NULL auto_increment,
  `Type` varchar(100) default NULL,
  `Value` bigint(20) default NULL,
  `CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;我现在想将以下命令返回的结果插入到上边这个表中：SHOW  STATUS LIKE 'Com_commit';
请问如何用sql 实现？

Answer 1

mysql -uroot -p1234 mysql -e "SHOW STATUS LIKE 'Com_commit';" > xxxx.dat
再用 LOAD DATA导入

Answer 2

假设表已经建立
bat文件：
mysql -uroot -p1234 mysql -e "SHOW STATUS LIKE 'Com_commit';" > xxxx.dat
mysql -uroot -p1234 mysql -e "LOAD DATA INFILE 'data.txt' INTO TABLE `mysql_monitor`;" 

Answer 3

好像不能写出sql,只有楼上的办法了,不过在load data infile (ignore/replace) into 可以加 
ignore/replace 这2个参数

Answer 4

怎么用？mysql不懂，写个例子？

Answer 5

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values: 如果没有唯一键的话，可以不用这两个参数

Answer 6

你可以通过如下SQL语句得到。
mysql> select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Com_commit';
+---------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+----------------+
| COM_COMMIT    | 0              |
+---------------+----------------+
1 row in set (0.00 sec)mysql>然后你可以insert into mysql_monitor .... select .... from information_schema.GLOBAL_STATUS

Answer 7

竟然也有 information_schema.GLOBAL_STATUS 这张表.哈哈哈.手册看少了,回头继续看次;

Answer 8

我这里提示：
[SQL] select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Com_commit';
[Err] 1109 - Unknown table 'global_status' in information_schema什么问题？

Answer 9

到information_schema库下面看看有没有这表?

Answer 10

status;
use  information_schema;
show tables;贴出来看看，

Answer 11

mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)Connection id:          333252
Current database:       information_schema
Current user:           [email protected]
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-nt
Protocol version:       10
Connection:             192.168.0.63 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 98 days 12 hours 43 min 16 secThreads: 7  Questions: 25168169  Slow queries: 0  Opens: 0  Flush tables: 1  Ope
n tables: 57  Queries per second avg: 2.956
--------------mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| KEY_COLUMN_USAGE                      |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
16 rows in set (0.00 sec)

Answer 12

mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
+---------------------------------------+
28 rows in set (0.00 sec)
明显楼主少了很多表;

Answer 13

为什么呢？权限都给了，用root进去，也是16个表。

Answer 14

你的MYSQL的版本问题。 在5.0 中还没有这几个表。这样就只能1. 升级你的MYSQL
2. 使用操作系统的脚本或者程序来实现。