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 实现?
我有一个表:
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 实现?
再用 LOAD DATA导入
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`;"
ignore/replace 这2个参数
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
竟然也有 information_schema.GLOBAL_STATUS 这张表.哈哈哈.手册看少了,回头继续看次;
[SQL] select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='Com_commit';
[Err] 1109 - Unknown table 'global_status' in information_schema什么问题?
use information_schema;
show tables;贴出来看看,
--------------
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)
+---------------------------------------+
| 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)
明显楼主少了很多表;
2. 使用操作系统的脚本或者程序来实现。