显示已经创建的库，创建时的语句。
mysql> show create database test; +----------+---------------------------------------------------------------+ | Database | Create Database | +----------+---------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+---------------------------------------------------------------+ 1 row in set (0.00 sec)
显示已经创建的事件，创建时的语句。
mysql> show create event event_test \G *************************** 1. row *************************** Event: event_test sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION time_zone: SYSTEM Create Event: CREATE DEFINER=`root`@`%` EVENT `event_test` ON SCHEDULE EVERY 1 SECOND STARTS '2020-03-13 09:39:53' ON COMPLETION PRESERVE ENABLE DO CALL test_pro() character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
注意：事件和ORACLE中的JOB类似，是定时任务。
显示已经创建的函数，创建时的语句
mysql> show create function func_find_pw \G *************************** 1. row *************************** Function: func_find_pw sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`%` FUNCTION `func_find_pw`(`id` int) RETURNS varchar(50) CHARSET utf8 BEGIN #Routine body goes here... declare v_pw varchar(100); select `password` into v_pw from `md5s` a where a.id=id; RETURN v_pw; END character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
显示已经创建的存储过程，创建时的语句
mysql> show create procedure pro_get_pwd \G *************************** 1. row *************************** Procedure: pro_get_pwd sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `pro_get_pwd`(IN `id` integer, INOUT `pwd` varchar(100)) BEGIN #Routine body goes here... select `password` into pwd from `md5s` a where a.id=id; END character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
显示已经创建的表，创建时的语句
mysql> show create table md5s \G *************************** 1. row *************************** Table: md5s Create Table: CREATE TABLE `md5s` ( `id` int(11) NOT NULL, `password` varchar(255) DEFAULT NULL, `length` int(11) DEFAULT NULL, `md5` varchar(32) DEFAULT NULL, `base64` varchar(1000) DEFAULT NULL, `create_time` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_password` (`password`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
显示已经创建的触发器，创建时的语句
mysql> show create trigger tri_insert_user \G *************************** 1. row *************************** Trigger: tri_insert_user sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`root`@`%` TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin call test_pro(); end character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: utf8_general_ci Created: 2020-03-13 09:44:44.76 1 row in set (0.00 sec)
显示已经创建的视图，创建时的语句
mysql> show create view view_pw_count \G *************************** 1. row *************************** View: view_pw_count Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_pw_count` AS select `t`.`password` AS `password`,`t`.`pw_count` AS `pw_count` from (select `test`.`md5s`.`password` AS `password`,count(0) AS `pw_count` from `test`.`md5s` group by `test`.`md5s`.`password`) `t` order by `t`.`pw_count` desc character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec)
注意：上面的 \G 用来格式化输出
