893 lines
35 KiB
Plaintext
893 lines
35 KiB
Plaintext
# ========== routines.1 ==========
|
|
USE INFORMATION_SCHEMA;
|
|
SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES;
|
|
View Create View character_set_client collation_connection
|
|
ROUTINES CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `ROUTINES` AS select `rtn`.`name` AS `SPECIFIC_NAME`,`cat`.`name` AS `ROUTINE_CATALOG`,`sch`.`name` AS `ROUTINE_SCHEMA`,`rtn`.`name` AS `ROUTINE_NAME`,`rtn`.`type` AS `ROUTINE_TYPE`,if((`rtn`.`type` = 'PROCEDURE'),'',substring_index(substring_index(`rtn`.`result_data_type_utf8`,'(',1),' ',1)) AS `DATA_TYPE`,internal_dd_char_length(`rtn`.`result_data_type`,`rtn`.`result_char_length`,`coll_result`.`name`,0) AS `CHARACTER_MAXIMUM_LENGTH`,internal_dd_char_length(`rtn`.`result_data_type`,`rtn`.`result_char_length`,`coll_result`.`name`,1) AS `CHARACTER_OCTET_LENGTH`,`rtn`.`result_numeric_precision` AS `NUMERIC_PRECISION`,`rtn`.`result_numeric_scale` AS `NUMERIC_SCALE`,`rtn`.`result_datetime_precision` AS `DATETIME_PRECISION`,(case `rtn`.`result_data_type` when 'MYSQL_TYPE_STRING' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) when 'MYSQL_TYPE_SET' then if((`cs_result`.`name` = 'binary'),NULL,`cs_result`.`name`) else NULL end) AS `CHARACTER_SET_NAME`,(case `rtn`.`result_data_type` when 'MYSQL_TYPE_STRING' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) when 'MYSQL_TYPE_SET' then if((`cs_result`.`name` = 'binary'),NULL,`coll_result`.`name`) else NULL end) AS `COLLATION_NAME`,if((`rtn`.`type` = 'PROCEDURE'),NULL,`rtn`.`result_data_type_utf8`) AS `DTD_IDENTIFIER`,'SQL' AS `ROUTINE_BODY`,if(can_access_routine(`sch`.`name`,`rtn`.`name`,`rtn`.`type`,`rtn`.`definer`,true),`rtn`.`definition_utf8`,NULL) AS `ROUTINE_DEFINITION`,NULL AS `EXTERNAL_NAME`,`rtn`.`external_language` AS `EXTERNAL_LANGUAGE`,'SQL' AS `PARAMETER_STYLE`,if((`rtn`.`is_deterministic` = 0),'NO','YES') AS `IS_DETERMINISTIC`,`rtn`.`sql_data_access` AS `SQL_DATA_ACCESS`,NULL AS `SQL_PATH`,`rtn`.`security_type` AS `SECURITY_TYPE`,`rtn`.`created` AS `CREATED`,`rtn`.`last_altered` AS `LAST_ALTERED`,`rtn`.`sql_mode` AS `SQL_MODE`,`rtn`.`comment` AS `ROUTINE_COMMENT`,`rtn`.`definer` AS `DEFINER`,`cs_client`.`name` AS `CHARACTER_SET_CLIENT`,`coll_conn`.`name` AS `COLLATION_CONNECTION`,`coll_db`.`name` AS `DATABASE_COLLATION` from ((((((((`mysql`.`routines` `rtn` join `mysql`.`schemata` `sch` on((`rtn`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll_client` on((`coll_client`.`id` = `rtn`.`client_collation_id`))) join `mysql`.`character_sets` `cs_client` on((`cs_client`.`id` = `coll_client`.`character_set_id`))) join `mysql`.`collations` `coll_conn` on((`coll_conn`.`id` = `rtn`.`connection_collation_id`))) join `mysql`.`collations` `coll_db` on((`coll_db`.`id` = `rtn`.`schema_collation_id`))) left join `mysql`.`collations` `coll_result` on((`coll_result`.`id` = `rtn`.`result_collation_id`))) left join `mysql`.`character_sets` `cs_result` on((`cs_result`.`id` = `coll_result`.`character_set_id`))) where (0 <> can_access_routine(`sch`.`name`,`rtn`.`name`,`rtn`.`type`,`rtn`.`definer`,false)) utf8 utf8_general_ci
|
|
SELECT * FROM information_schema.columns
|
|
WHERE table_schema = 'information_schema'
|
|
AND table_name = 'ROUTINES'
|
|
ORDER BY ordinal_position;
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME SPECIFIC_NAME
|
|
ORDINAL_POSITION 1
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_CATALOG
|
|
ORDINAL_POSITION 2
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_SCHEMA
|
|
ORDINAL_POSITION 3
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_NAME
|
|
ORDINAL_POSITION 4
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_TYPE
|
|
ORDINAL_POSITION 5
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE enum
|
|
CHARACTER_MAXIMUM_LENGTH 9
|
|
CHARACTER_OCTET_LENGTH 27
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE enum('FUNCTION','PROCEDURE')
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME DATA_TYPE
|
|
ORDINAL_POSITION 6
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE longtext
|
|
CHARACTER_MAXIMUM_LENGTH 4294967295
|
|
CHARACTER_OCTET_LENGTH 4294967295
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE longtext
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH
|
|
ORDINAL_POSITION 7
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE bigint
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION 19
|
|
NUMERIC_SCALE 0
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE bigint(21)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME CHARACTER_OCTET_LENGTH
|
|
ORDINAL_POSITION 8
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE bigint
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION 19
|
|
NUMERIC_SCALE 0
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE bigint(21)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME NUMERIC_PRECISION
|
|
ORDINAL_POSITION 9
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE int
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION 10
|
|
NUMERIC_SCALE 0
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE int(10) unsigned
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME NUMERIC_SCALE
|
|
ORDINAL_POSITION 10
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE int
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION 10
|
|
NUMERIC_SCALE 0
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE int(10) unsigned
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME DATETIME_PRECISION
|
|
ORDINAL_POSITION 11
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE int
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION 10
|
|
NUMERIC_SCALE 0
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE int(10) unsigned
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME CHARACTER_SET_NAME
|
|
ORDINAL_POSITION 12
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME COLLATION_NAME
|
|
ORDINAL_POSITION 13
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME DTD_IDENTIFIER
|
|
ORDINAL_POSITION 14
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE longtext
|
|
CHARACTER_MAXIMUM_LENGTH 4294967295
|
|
CHARACTER_OCTET_LENGTH 4294967295
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE longtext
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_BODY
|
|
ORDINAL_POSITION 15
|
|
COLUMN_DEFAULT
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 3
|
|
CHARACTER_OCTET_LENGTH 9
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(3)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_DEFINITION
|
|
ORDINAL_POSITION 16
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE longtext
|
|
CHARACTER_MAXIMUM_LENGTH 4294967295
|
|
CHARACTER_OCTET_LENGTH 4294967295
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE longtext
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME EXTERNAL_NAME
|
|
ORDINAL_POSITION 17
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE binary
|
|
CHARACTER_MAXIMUM_LENGTH 0
|
|
CHARACTER_OCTET_LENGTH 0
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE binary(0)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME EXTERNAL_LANGUAGE
|
|
ORDINAL_POSITION 18
|
|
COLUMN_DEFAULT SQL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME PARAMETER_STYLE
|
|
ORDINAL_POSITION 19
|
|
COLUMN_DEFAULT
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 3
|
|
CHARACTER_OCTET_LENGTH 9
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(3)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME IS_DETERMINISTIC
|
|
ORDINAL_POSITION 20
|
|
COLUMN_DEFAULT
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 3
|
|
CHARACTER_OCTET_LENGTH 9
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(3)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME SQL_DATA_ACCESS
|
|
ORDINAL_POSITION 21
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE enum
|
|
CHARACTER_MAXIMUM_LENGTH 17
|
|
CHARACTER_OCTET_LENGTH 51
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE enum('CONTAINS SQL','NO SQL','READS SQL DATA','MODIFIES SQL DATA')
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME SQL_PATH
|
|
ORDINAL_POSITION 22
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE YES
|
|
DATA_TYPE binary
|
|
CHARACTER_MAXIMUM_LENGTH 0
|
|
CHARACTER_OCTET_LENGTH 0
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE binary(0)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME SECURITY_TYPE
|
|
ORDINAL_POSITION 23
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE enum
|
|
CHARACTER_MAXIMUM_LENGTH 7
|
|
CHARACTER_OCTET_LENGTH 21
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE enum('DEFAULT','INVOKER','DEFINER')
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME CREATED
|
|
ORDINAL_POSITION 24
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE timestamp
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION 0
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE timestamp
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME LAST_ALTERED
|
|
ORDINAL_POSITION 25
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE timestamp
|
|
CHARACTER_MAXIMUM_LENGTH NULL
|
|
CHARACTER_OCTET_LENGTH NULL
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION 0
|
|
CHARACTER_SET_NAME NULL
|
|
COLLATION_NAME NULL
|
|
COLUMN_TYPE timestamp
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME SQL_MODE
|
|
ORDINAL_POSITION 26
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE set
|
|
CHARACTER_MAXIMUM_LENGTH 520
|
|
CHARACTER_OCTET_LENGTH 1560
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL')
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME ROUTINE_COMMENT
|
|
ORDINAL_POSITION 27
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE text
|
|
CHARACTER_MAXIMUM_LENGTH 65535
|
|
CHARACTER_OCTET_LENGTH 65535
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE text
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME DEFINER
|
|
ORDINAL_POSITION 28
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 288
|
|
CHARACTER_OCTET_LENGTH 864
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_bin
|
|
COLUMN_TYPE varchar(288)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME CHARACTER_SET_CLIENT
|
|
ORDINAL_POSITION 29
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME COLLATION_CONNECTION
|
|
ORDINAL_POSITION 30
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
TABLE_CATALOG def
|
|
TABLE_SCHEMA information_schema
|
|
TABLE_NAME ROUTINES
|
|
COLUMN_NAME DATABASE_COLLATION
|
|
ORDINAL_POSITION 31
|
|
COLUMN_DEFAULT NULL
|
|
IS_NULLABLE NO
|
|
DATA_TYPE varchar
|
|
CHARACTER_MAXIMUM_LENGTH 64
|
|
CHARACTER_OCTET_LENGTH 192
|
|
NUMERIC_PRECISION NULL
|
|
NUMERIC_SCALE NULL
|
|
DATETIME_PRECISION NULL
|
|
CHARACTER_SET_NAME utf8
|
|
COLLATION_NAME utf8_general_ci
|
|
COLUMN_TYPE varchar(64)
|
|
COLUMN_KEY
|
|
EXTRA
|
|
PRIVILEGES select
|
|
COLUMN_COMMENT
|
|
GENERATION_EXPRESSION
|
|
SRS_ID NULL
|
|
DESCRIBE INFORMATION_SCHEMA.ROUTINES;
|
|
Field Type Null Key Default Extra
|
|
SPECIFIC_NAME varchar(64) NO NULL
|
|
ROUTINE_CATALOG varchar(64) NO NULL
|
|
ROUTINE_SCHEMA varchar(64) NO NULL
|
|
ROUTINE_NAME varchar(64) NO NULL
|
|
ROUTINE_TYPE enum('FUNCTION','PROCEDURE') NO NULL
|
|
DATA_TYPE longtext YES NULL
|
|
CHARACTER_MAXIMUM_LENGTH bigint(21) YES NULL
|
|
CHARACTER_OCTET_LENGTH bigint(21) YES NULL
|
|
NUMERIC_PRECISION int(10) unsigned YES NULL
|
|
NUMERIC_SCALE int(10) unsigned YES NULL
|
|
DATETIME_PRECISION int(10) unsigned YES NULL
|
|
CHARACTER_SET_NAME varchar(64) YES NULL
|
|
COLLATION_NAME varchar(64) YES NULL
|
|
DTD_IDENTIFIER longtext YES NULL
|
|
ROUTINE_BODY varchar(3) NO
|
|
ROUTINE_DEFINITION longtext YES NULL
|
|
EXTERNAL_NAME binary(0) YES NULL
|
|
EXTERNAL_LANGUAGE varchar(64) NO SQL
|
|
PARAMETER_STYLE varchar(3) NO
|
|
IS_DETERMINISTIC varchar(3) NO
|
|
SQL_DATA_ACCESS enum('CONTAINS SQL','NO SQL','READS SQL DATA','MODIFIES SQL DATA') NO NULL
|
|
SQL_PATH binary(0) YES NULL
|
|
SECURITY_TYPE enum('DEFAULT','INVOKER','DEFINER') NO NULL
|
|
CREATED timestamp NO NULL
|
|
LAST_ALTERED timestamp NO NULL
|
|
SQL_MODE set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NO NULL
|
|
ROUTINE_COMMENT text NO NULL
|
|
DEFINER varchar(288) NO NULL
|
|
CHARACTER_SET_CLIENT varchar(64) NO NULL
|
|
COLLATION_CONNECTION varchar(64) NO NULL
|
|
DATABASE_COLLATION varchar(64) NO NULL
|
|
# ========== routines.2 ==========
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50)
|
|
RETURN CONCAT('Hello', ,s,'!');
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNS CHAR(50)
|
|
RETURN CONCAT('Hello', ,s,'!')' at line 1
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
# ========== routines.3 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
|
|
RETURN CONCAT('Hello, ',s,'!');
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func1 def i_s_routines_test test_func1 FUNCTION char 50 200 NULL NULL NULL utf8mb4 utf8mb4_general_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
DROP FUNCTION test_func1;
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
# ========== routines.4 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE PROCEDURE testproc (OUT param1 INT)
|
|
BEGIN
|
|
SELECT 2+2 as param1;
|
|
END;
|
|
//
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'testproc';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
testproc def i_s_routines_test testproc PROCEDURE NULL NULL NULL NULL NULL NULL NULL NULL SQL BEGIN
|
|
SELECT 2+2 as param1;
|
|
END NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
# ========== routines.5 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
|
|
RETURN CONCAT('Hello, ',s,'!');
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func1';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func1 def i_s_routines_test test_func1 FUNCTION char 50 200 NULL NULL NULL utf8mb4 utf8mb4_general_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
# ========== routines.6 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2;
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func2';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func2 def i_s_routines_test test_func2 FUNCTION int NULL NULL 10 0 NULL NULL NULL int(11) SQL RETURN s*2 NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
# ========== routines.7 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
|
|
RETURN CURRENT_TIMESTAMP;
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL 0 NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
# ========== routines.8 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
|
|
RETURN CURRENT_TIMESTAMP;
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL 0 NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
ALTER FUNCTION test_func5 COMMENT 'new comment added';
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func5 def i_s_routines_test test_func5 FUNCTION timestamp NULL NULL NULL NULL 0 NULL NULL timestamp SQL RETURN CURRENT_TIMESTAMP NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION new comment added root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8mb4_general_ci
|
|
# ========== routines.9 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test CHARACTER SET utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
USE i_s_routines_test;
|
|
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
|
|
RETURN CONCAT('XYZ, ' ,s);
|
|
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = 'i_s_routines_test' AND ROUTINE_NAME = 'test_func5';
|
|
SPECIFIC_NAME ROUTINE_CATALOG ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_BODY ROUTINE_DEFINITION EXTERNAL_NAME EXTERNAL_LANGUAGE PARAMETER_STYLE IS_DETERMINISTIC SQL_DATA_ACCESS SQL_PATH SECURITY_TYPE CREATED LAST_ALTERED SQL_MODE ROUTINE_COMMENT DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION
|
|
test_func5 def i_s_routines_test test_func5 FUNCTION varchar 30 90 NULL NULL NULL utf8 utf8_general_ci varchar(30) SQL RETURN CONCAT('XYZ, ' ,s) NULL SQL SQL NO CONTAINS SQL NULL DEFINER <created> <modified> ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION root@localhost utf8mb4 utf8mb4_0900_ai_ci utf8_general_ci
|
|
# ========== routines.10 ==========
|
|
DROP DATABASE IF EXISTS i_s_routines_test;
|
|
CREATE DATABASE i_s_routines_test;
|
|
USE i_s_routines_test;
|
|
CREATE PROCEDURE test_proc1 (OUT p_p1 timestamp) BEGIN SELECT 1 ; END; \\
|
|
CREATE FUNCTION test_func_t (f_p1 DATETIME) RETURNS TIME RETURN NULL;
|
|
CREATE FUNCTION test_func_dt (f_p1 DATETIME) RETURNS DATETIME RETURN NULL;
|
|
CREATE FUNCTION test_func_ts (f_p1 DATETIME) RETURNS TIMESTAMP RETURN NULL;
|
|
CREATE FUNCTION test_func_t0 (f_p1 DATETIME) RETURNS TIME(0) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt0 (f_p1 DATETIME) RETURNS DATETIME(0) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts0 (f_p1 DATETIME) RETURNS TIMESTAMP(0) RETURN NULL;
|
|
CREATE FUNCTION test_func_t1 (f_p1 DATETIME) RETURNS TIME(1) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt1 (f_p1 DATETIME) RETURNS DATETIME(1) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts1 (f_p1 DATETIME) RETURNS TIMESTAMP(1) RETURN NULL;
|
|
CREATE FUNCTION test_func_t2 (f_p1 DATETIME) RETURNS TIME(2) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt2 (f_p1 DATETIME) RETURNS DATETIME(2) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts2 (f_p1 DATETIME) RETURNS TIMESTAMP(2) RETURN NULL;
|
|
CREATE FUNCTION test_func_t3 (f_p1 DATETIME) RETURNS TIME(3) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt3 (f_p1 DATETIME) RETURNS DATETIME(3) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts3 (f_p1 DATETIME) RETURNS TIMESTAMP(3) RETURN NULL;
|
|
CREATE FUNCTION test_func_t4 (f_p1 DATETIME) RETURNS TIME(4) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt4 (f_p1 DATETIME) RETURNS DATETIME(4) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts4 (f_p1 DATETIME) RETURNS TIMESTAMP(4) RETURN NULL;
|
|
CREATE FUNCTION test_func_t5 (f_p1 DATETIME) RETURNS TIME(5) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt5 (f_p1 DATETIME) RETURNS DATETIME(5) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts5 (f_p1 DATETIME) RETURNS TIMESTAMP(5) RETURN NULL;
|
|
CREATE FUNCTION test_func_t6 (f_p1 DATETIME) RETURNS TIME(6) RETURN NULL;
|
|
CREATE FUNCTION test_func_dt6 (f_p1 DATETIME) RETURNS DATETIME(6) RETURN NULL;
|
|
CREATE FUNCTION test_func_ts6 (f_p1 DATETIME) RETURNS TIMESTAMP(6) RETURN NULL;
|
|
SELECT ROUTINE_NAME, ROUTINE_TYPE, DATA_TYPE, DATETIME_PRECISION from
|
|
INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='i_s_routines_test';
|
|
ROUTINE_NAME ROUTINE_TYPE DATA_TYPE DATETIME_PRECISION
|
|
test_func_dt FUNCTION datetime 0
|
|
test_func_dt0 FUNCTION datetime 0
|
|
test_func_dt1 FUNCTION datetime 1
|
|
test_func_dt2 FUNCTION datetime 2
|
|
test_func_dt3 FUNCTION datetime 3
|
|
test_func_dt4 FUNCTION datetime 4
|
|
test_func_dt5 FUNCTION datetime 5
|
|
test_func_dt6 FUNCTION datetime 6
|
|
test_func_t FUNCTION time 0
|
|
test_func_t0 FUNCTION time 0
|
|
test_func_t1 FUNCTION time 1
|
|
test_func_t2 FUNCTION time 2
|
|
test_func_t3 FUNCTION time 3
|
|
test_func_t4 FUNCTION time 4
|
|
test_func_t5 FUNCTION time 5
|
|
test_func_t6 FUNCTION time 6
|
|
test_func_ts FUNCTION timestamp 0
|
|
test_func_ts0 FUNCTION timestamp 0
|
|
test_func_ts1 FUNCTION timestamp 1
|
|
test_func_ts2 FUNCTION timestamp 2
|
|
test_func_ts3 FUNCTION timestamp 3
|
|
test_func_ts4 FUNCTION timestamp 4
|
|
test_func_ts5 FUNCTION timestamp 5
|
|
test_func_ts6 FUNCTION timestamp 6
|
|
test_proc1 PROCEDURE NULL
|
|
DROP DATABASE i_s_routines_test;
|
|
USE test;
|
|
#
|
|
# Bug #21632425: SHOW FUNCTION STATUS ASSERTS WITH
|
|
# PAD_CHAR_TO_FULL_LENGTH
|
|
#
|
|
SELECT @@sql_mode INTO @old_sql_mode;
|
|
SET sql_mode = 'pad_char_to_full_length';
|
|
Warnings:
|
|
Warning 3090 Changing sql mode 'PAD_CHAR_TO_FULL_LENGTH' is deprecated. It will be removed in a future release.
|
|
CREATE FUNCTION f() RETURNS INT RETURN 1;
|
|
# Must not assert
|
|
SHOW FUNCTION STATUS;
|
|
DROP FUNCTION IF EXISTS f;
|
|
SET sql_mode = @old_sql_mode;
|
|
# End of 5.7 tests
|