set sql_mode=""; set sql_mode=""; # ========== routines.1 ========== USE INFORMATION_SCHEMA; SHOW CREATE TABLE INFORMATION_SCHEMA.ROUTINES; Table Create Table ROUTINES CREATE TEMPORARY TABLE `ROUTINES` ( `SPECIFIC_NAME` varchar(64) NOT NULL, `ROUTINE_CATALOG` varchar(512) NOT NULL, `ROUTINE_SCHEMA` varchar(64) NOT NULL, `ROUTINE_NAME` varchar(64) NOT NULL, `ROUTINE_TYPE` varchar(13) NOT NULL, `DATA_TYPE` varchar(64) NOT NULL, `CHARACTER_MAXIMUM_LENGTH` int(21), `CHARACTER_OCTET_LENGTH` int(21), `NUMERIC_PRECISION` int(21), `NUMERIC_SCALE` int(21), `DATETIME_PRECISION` bigint(21) unsigned, `CHARACTER_SET_NAME` varchar(64), `COLLATION_NAME` varchar(64), `DTD_IDENTIFIER` longtext, `ROUTINE_BODY` varchar(8) NOT NULL, `ROUTINE_DEFINITION` longtext, `EXTERNAL_NAME` varchar(64), `EXTERNAL_LANGUAGE` varchar(64), `PARAMETER_STYLE` varchar(8) NOT NULL, `IS_DETERMINISTIC` varchar(3) NOT NULL, `SQL_DATA_ACCESS` varchar(64) NOT NULL, `SQL_PATH` varchar(64), `SECURITY_TYPE` varchar(7) NOT NULL, `CREATED` datetime NOT NULL, `LAST_ALTERED` datetime NOT NULL, `SQL_MODE` varchar(8192) NOT NULL, `ROUTINE_COMMENT` longtext NOT NULL, `DEFINER` varchar(189) NOT NULL, `CHARACTER_SET_CLIENT` varchar(32) NOT NULL, `COLLATION_CONNECTION` varchar(32) NOT NULL, `DATABASE_COLLATION` varchar(32) NOT NULL ) DEFAULT CHARSET=utf8 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 512 CHARACTER_OCTET_LENGTH 1536 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(512) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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_general_ci COLUMN_TYPE varchar(64) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 varchar CHARACTER_MAXIMUM_LENGTH 13 CHARACTER_OCTET_LENGTH 39 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(13) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME DATA_TYPE ORDINAL_POSITION 6 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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(21) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 bigint CHARACTER_MAXIMUM_LENGTH NULL CHARACTER_OCTET_LENGTH NULL NUMERIC_PRECISION 20 NUMERIC_SCALE 0 DATETIME_PRECISION NULL CHARACTER_SET_NAME NULL COLLATION_NAME NULL COLUMN_TYPE bigint(21) unsigned COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME ROUTINE_BODY ORDINAL_POSITION 15 COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 CHARACTER_OCTET_LENGTH 24 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME EXTERNAL_LANGUAGE ORDINAL_POSITION 18 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME PARAMETER_STYLE ORDINAL_POSITION 19 COLUMN_DEFAULT NULL IS_NULLABLE NO DATA_TYPE varchar CHARACTER_MAXIMUM_LENGTH 8 CHARACTER_OCTET_LENGTH 24 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL TABLE_CATALOG def TABLE_SCHEMA information_schema TABLE_NAME ROUTINES COLUMN_NAME IS_DETERMINISTIC ORDINAL_POSITION 20 COLUMN_DEFAULT NULL 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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 # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 varchar CHARACTER_MAXIMUM_LENGTH 7 CHARACTER_OCTET_LENGTH 21 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(7) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 datetime 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 datetime COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 datetime 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 datetime COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 varchar CHARACTER_MAXIMUM_LENGTH 8192 CHARACTER_OCTET_LENGTH 24576 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(8192) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 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_general_ci COLUMN_TYPE longtext COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 189 CHARACTER_OCTET_LENGTH 567 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(189) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION 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 32 CHARACTER_OCTET_LENGTH 96 NUMERIC_PRECISION NULL NUMERIC_SCALE NULL DATETIME_PRECISION NULL CHARACTER_SET_NAME utf8 COLLATION_NAME utf8_general_ci COLUMN_TYPE varchar(32) COLUMN_KEY EXTRA PRIVILEGES # COLUMN_COMMENT IS_GENERATED NEVER GENERATION_EXPRESSION NULL DESCRIBE INFORMATION_SCHEMA.ROUTINES; Field Type Null Key Default Extra SPECIFIC_NAME varchar(64) NO NULL ROUTINE_CATALOG varchar(512) NO NULL ROUTINE_SCHEMA varchar(64) NO NULL ROUTINE_NAME varchar(64) NO NULL ROUTINE_TYPE varchar(13) NO NULL DATA_TYPE varchar(64) NO NULL CHARACTER_MAXIMUM_LENGTH int(21) YES NULL CHARACTER_OCTET_LENGTH int(21) YES NULL NUMERIC_PRECISION int(21) YES NULL NUMERIC_SCALE int(21) YES NULL DATETIME_PRECISION bigint(21) unsigned YES NULL CHARACTER_SET_NAME varchar(64) YES NULL COLLATION_NAME varchar(64) YES NULL DTD_IDENTIFIER longtext YES NULL ROUTINE_BODY varchar(8) NO NULL ROUTINE_DEFINITION longtext YES NULL EXTERNAL_NAME varchar(64) YES NULL EXTERNAL_LANGUAGE varchar(64) YES NULL PARAMETER_STYLE varchar(8) NO NULL IS_DETERMINISTIC varchar(3) NO NULL SQL_DATA_ACCESS varchar(64) NO NULL SQL_PATH varchar(64) YES NULL SECURITY_TYPE varchar(7) NO NULL CREATED datetime NO NULL LAST_ALTERED datetime NO NULL SQL_MODE varchar(8192) NO NULL ROUTINE_COMMENT longtext NO NULL DEFINER varchar(189) NO NULL CHARACTER_SET_CLIENT varchar(32) NO NULL COLLATION_CONNECTION varchar(32) NO NULL DATABASE_COLLATION varchar(32) NO NULL # ========== routines.2 ========== 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,'!'); ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB 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 50 NULL NULL NULL latin1 latin1_swedish_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 50 NULL NULL NULL latin1 latin1_swedish_ci char(50) SQL RETURN CONCAT('Hello, ',s,'!') NULL NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci latin1_swedish_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 NULL SQL NO CONTAINS SQL NULL DEFINER new comment added root@localhost latin1 latin1_swedish_ci latin1_swedish_ci # ========== routines.9 ========== DROP DATABASE IF EXISTS i_s_routines_test; CREATE DATABASE i_s_routines_test CHARACTER SET utf8; 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 NULL SQL NO CONTAINS SQL NULL DEFINER root@localhost latin1 latin1_swedish_ci utf8_general_ci DROP DATABASE i_s_routines_test;