SET =concat( "INSERT IGNORE INTO ", in_db, ". SET func_query = trim(trailing ', ' FROM func_query) WHEN col_datatype in ( 'GEOMETRY ', 'POINT ', 'LINESTRING ', 'POLYGON ', 'MULTIPOINT ', 'MULTILINESTRING ', 'MULTIPOLYGON ', 'GEOMETRYCOLLECTION ') THEN SET func_query =concat(func_query, 'NULL, ') ĮLSE SET func_query =concat(func_query, 'get_varchar( ',ifnull(col_maxlen, 0), '), ') WHEN col_datatype in ( 'enum ', 'set ') THEN SET func_query =concat(func_query, 'get_enum(" ',col_type, '"), ') WHEN col_datatype in ( 'float ', 'decimal ') THEN SET func_query =concat(func_query, 'get_float( ',col_num_precision, ', ',col_num_scale, '), ') WHEN col_datatype in ( 'date ') THEN SET func_query =concat(func_query, 'get_date(), ') WHEN col_datatype in ( 'datetime ', 'timestamp ') THEN SET func_query =concat(func_query, 'get_datetime(), ') WHEN col_datatype in ( 'tinyint ', 'smallint ', 'year ') or col_datatype = 'mediumint ' THEN SET func_query =concat(func_query, 'get_tinyint(), ') WHEN col_datatype in ( 'varchar ', 'char ') THEN SET func_query =concat(func_query, 'get_string( ',ifnull(col_maxlen, 0), '), ') WHEN col_datatype in ( 'double ', 'int ', 'bigint ') THEN SET func_query =concat(func_query, 'get_int(), ') WHEN col_extra = 'auto_increment ' THEN SET func_query =concat(func_query, 'NULL, ') #SELECT CONCAT(col_name,"-", col_type,"-", col_datatype,"-", IFNULL(col_maxlen,'NULL'),"-", IFNULL(col_extra,'NULL')) AS VALS columns WHERE table_name =in_table AND table_schema =in_db ORDER BY ORDINAL_POSITION ĭECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1 įETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_schema. | The data is being loaded in bulk of 500 rows which is hardcoded for now. | Debug-mode will print an SQL that's executed and iterated. | EXAMPLE: call populate('sakila','film',100,'N') | USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE) CREATE PROCEDURE populate(in_db varchar( 100), in_table varchar( 100), in_rows int, in_debug char( 1))
0 Comments
Leave a Reply. |