¿Por qué no puedo crear una tabla temporal en el procedimiento almacenado de MySQL?
1. Motor de tablas temporales MySQL, denominado Memoria. Por ejemplo
Crear tabla tmp1(id int, str 1 varchar(100))engine = memoria;
Controlado por el parámetro max_heap_table_size, se excede el informe de errores.
2. Los motores de tablas no temporales se dividen en dos categorías:
Tablas temporales definidas por el usuario, por ejemplo:
Crear tabla temporal (id int, str 1 varchar (100));
Tablas temporales internas generadas durante la ejecución de SQL, como: UNION, clase agregada ORDER BY, tablas derivadas, consultas de campos de objetos grandes, subconsultas o solidificación de semiunión, etc.
¿Entonces se suelen utilizar los contadores de estas dos tablas temporales? Muestra el estado global, como "% tmp _% tablas%" para ver. Por ejemplo
mysql & gt muestra un estado similar a "%tmp_%tables%";` `+-+`|¿Nombre de variable? | Valor | ` `+-+-+`| Creado _ tmp _ tablas 0 | ` `| ¿Creado _ tmp _? | 0 | ` ``+-+`` 2 filas en la colección (0,00 segundos)
Los resultados anteriores indican que solo se crearon el recuento de tablas temporales en el disco y el número total de tablas temporales. Estos dos contadores están controlados por el valor mínimo de los parámetros tmp_table_size y max_heap_table_size.
Antes de MySQL 5.7, la tabla temporal generada por esta operación SQL era MYISAM, y solo podía ser MYISAM. Luego, MySQL proporciona el parámetro internal_tmp_mem_storage_engine a partir de 5.7 para definir el motor de tabla temporal interno. Los valores opcionales son MYISAM e INNODB. Por supuesto, aquí elegimos INNODB. Y la tabla temporal interna se guarda en el espacio de tabla temporal ibtmp1 de forma predeterminada (puede usar parámetros como innodb_temp_data_file_path para establecer el tamaño y el tamaño del paso). Por supuesto, debemos controlar el tamaño de ibtmp1 aquí; de lo contrario, se producirá un SQL incorrecto. provocar un accidente.
Sin embargo, los siguientes problemas no se resolvían antes de MySQL 5.7:
El almacenamiento de longitud variable de VARCHAR. En otras palabras, si la definición de campo de la tabla temporal es VARCHAR(200), entonces el campo asignado a la memoria para su procesamiento se convierte en CHAR(200). Suponiendo que solo hay un carácter "y" en VARCHAR(200), ¿no sería un gran desperdicio?
Almacenamiento en disco predeterminado para objetos grandes, como TEXTO, BLOB, JSON, etc. , no importa lo que esté almacenado en él, se convierte directamente en almacenamiento en disco.
A partir de MySQL 8.0, el motor de tablas temporales TempTable se ha implementado especialmente para resolver el problema del almacenamiento de longitud lateral de campos VARCHAR y el almacenamiento de memoria de objetos grandes. Controlado por la variable Inter_TMP_MEM_Storage_Engine, los valores opcionales son TempTable (predeterminado) y Memoria; el tamaño del nuevo motor está controlado por el parámetro temp_table_max_ram, y el valor predeterminado es 1G. Si se excede, se almacena en el disco (ibtmp1). Y el contador se almacena en la tabla memoria_summary_global_by_event_name del diccionario de rendimiento.
mysql & gtSELECT * FROM performance_schema.
memoria_resumen_global_por_evento_nombre donde evento_nombre como '%temptable%' G * * * * * * * * * * * * * * * * * * * * * * * * * * 1. DE ACUERDO**************************? NOMBRE_EVENTO:* * memoria/temptable/disco_físico * * COUNT_ALLOC:0? COUNT_FREE: 0? SUMA _ NÚMERO _ DE _ BYTES _ ALLOC:0 SUM _ NÚMERO _ DE _ BYTES _ LIBRES:●CUENTA _ USADOS:0? Recuento actual utilizado: 0 Recuento alto utilizado: 0 Número bajo de bytes utilizados: 0 Número actual de bytes utilizados: 0? Número máximo de bytes utilizados: 0 * * * * * * * * * * * * * * * * * * * * * * * 2. DE ACUERDO**************************? EVENT_NAME:* * memoria/temptable/physical_ram * * COUNT_ALLOC:1? COUNT_FREE: 0? SUM_NUMBER_OF_BYTES_ALLOC:1048576 SUM_NUMBER_OF_BYTES_FREE:0? ●COUNT_USED:0? Recuento actual utilizado: 1 Recuento alto utilizado: 1 Recuento bajo de bytes utilizado: 0 Recuento de bytes actual utilizado: 1048576? Número máximo de bytes utilizados: 10485762 filas en la colección (0,03 segundos)
La memoria/temptable/disco_físico anterior representa el recuento de tablas temporales colocadas en el disco.
Memoria/temptable/physical_ram indica el número de tablas temporales colocadas en la memoria.
A continuación, resuma el motor TempTable introducido en MySQL 8.0:
El motor de tabla temporal interno predeterminado.
Admite el almacenamiento real de tipos de caracteres de longitud variable.
Establezca la variable temp_table_max_ram para controlar el tamaño del área de memoria de almacenamiento real.