![]() | ROUTINE_NAME | ROUTINE_BODY | param_list | WHERE p.specific_name = outertable.routine_nameįROM information_schema.routines outertable (SELECT group_concat(parameter_name || ' ' || dtd_identifier) Like this (with sql_mode=’pipes_as_concat’), but skipping the details we’ve already seen: We don’t need to do anything with parameter #0 (it’s just a copy of what’s in information_) we only need to merge parameter #1 and parameter #2 into the main query. | ORDINAL_POSITION | PARAMETER_MODE | PARAMETER_NAME | DTD_IDENTIFIER | SELECT ORDINAL_POSITION, PARAMETER_MODE, PARAMETER_NAME, DTD_IDENTIFIER It’s trickier to find a substitute for param_list, because parameters aren’t in information_schema.routines at all. Using DTD_IDENTIFIER avoids complications with other data types so I’ll always go with it. This isn’t too bad - all we have to do, (with sql_mode=’pipes_as_concat’) is concatenateĭATA_TYPE || ‘(‘ || CHARACTER_MAXIMUM_LENGTH || ‘)’ || ‘ CHARSET || CHARACTER_SET_NAMEĭTD_IDENTIFIER || ‘ CHARSET ‘ || CHARACTER_SET_NAMEĪnd we’ve got “char(5) CHARSET latin1”, the same as what’s in. | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_SET_NAME | DTD_IDENTIFIER | SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_SET_NAME, DTD_IDENTIFIER SELECT param_list, returns FROM mysql.proc WHERE name='fx1' Let’s look at it via mysql.proc (whenever I show mysql.proc I’m using MySQL 5.7): param_list and returnsĬREATE FUNCTION fx1(paramx1 INT, paramx2 DOUBLE) RETURNS CHAR(5) RETURN 'xxxxx' However, three mysql.proc columns - param_list, returns, body - have no corresponding columns in information_schema.routines. ![]() (By the way I used name rather than specific_name because it’s in the primary key the value is the same.) SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM information_schema.routines What the chart shows is that corresponds to information_SCHEMA, corresponds to information_NAME, and so on. But usually it will make no difference to either programmers or end users. This might affect applications that depend on exact size allocations and precise ordering. I have included the dirt about column data type and collation so that it’s clear they are never exactly the same. LAST_ALTERED: was datetime, will be timestampĭEFINER: was varchar(77) utf8_general_ci, will be varchar(93) utf8_binĬHARACTER_SET_CLIENT: was varchar(32), will be varchar(64)ĬOLLATION_CONNECTION: was varchar(32), will be varchar(64)ĭATABASE_COLLATION: was varchar(32), will be varchar(64) SECURITY_TYPE: was varchar(7), will be enum ![]() ![]() SQL_DATA_ACCESS: was varchar(64), will be enum ROUTINE_TYPE: was varchar(9) utf8_general_ci, will be enumĭATA_TYPE: was varchar(9), will be longtextĮXTERNAL_LANGUAGE: was varchar(64), will be binary(0) ROUTINE_SCHEMA: was varchar(64) utf8_general_ci, will be varchar(64) utf8_tolower_ci Remember the above chart is for MySQL version 5.7.įor MySQL 8.0.2 these column definitions have changed: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |