Diferencias

Muestra las diferencias entre dos versiones de la página.

--- notas:bases_de_datos:mysql [2015/04/22 14:41] – [Scripts] cayu
+++ notas:bases_de_datos:mysql [2020/09/18 23:41] – cayu
@@ Línea 68: / Línea 68: @@
 </code>
+==== Tamaños de bases y tablas ====
+<code sql>
+SELECT table_schema "DB Name",
+        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
+FROM information_schema.tables
+GROUP BY table_schema;
+</code>
+<code sql>
+SELECT table_name AS "Table",
+ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
+FROM information_schema.TABLES
+WHERE table_schema = "database_name"
+ORDER BY (data_length + index_length) DESC;
+</code>
+==== Migrar una base a InnoDB ====
+<code>
+# mysql base < mig_innodb.sql
+</code>
+<code sql>
+DROP PROCEDURE IF EXISTS convertToInnodb;
+DELIMITER //
+CREATE PROCEDURE convertToInnodb()
+BEGIN
+mainloop: LOOP
+  SELECT TABLE_NAME INTO @convertTable FROM information_schema.TABLES
+  WHERE `TABLE_SCHEMA` LIKE DATABASE()
+  AND `ENGINE` LIKE 'MyISAM' ORDER BY TABLE_NAME LIMIT 1;
+  IF @convertTable IS NULL THEN
+    LEAVE mainloop;
+  END IF;
+  SET @sqltext := CONCAT('ALTER TABLE `', DATABASE(), '`.`', @convertTable, '` ENGINE = INNODB');
+  PREPARE convertTables FROM @sqltext;
+  EXECUTE convertTables;
+  DEALLOCATE PREPARE convertTables;
+  SET @convertTable = NULL;
+END LOOP mainloop;
+END//
+DELIMITER ;
+CALL convertToInnodb();
+DROP PROCEDURE IF EXISTS convertToInnodb;
+</code>