innodb – Page 8 – Tarik Billa

How do I see all foreign keys to a table or column?

September 8, 2022 by Tarik

For a Table: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’; For a Column: SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA = ‘<database>’ AND REFERENCED_TABLE_NAME = ‘<table>’ AND REFERENCED_COLUMN_NAME = ‘<column>’; Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

MyISAM versus InnoDB [closed]

September 6, 2022 by Tarik

I have briefly discussed this question in a table so you can conclude whether to go with InnoDB or MyISAM. Here is a small overview of which db storage engine you should use in which situation: MyISAM InnoDB —————————————————————- Required full-text search Yes 5.6.4 —————————————————————- Require transactions Yes —————————————————————- Frequent select queries Yes —————————————————————- Frequent … Read more

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

September 6, 2022 by Tarik

From the documentation (MySQL 8) : Type | Maximum length ———–+————————————- TINYTEXT | 255 (2 8−1) bytes TEXT | 65,535 (216−1) bytes = 64 KiB MEDIUMTEXT | 16,777,215 (224−1) bytes = 16 MiB LONGTEXT | 4,294,967,295 (232−1) bytes = 4 GiB Note that the number of characters that can be stored in your column will … Read more

How do I quickly rename a MySQL database (change schema name)?

September 4, 2022 by Tarik

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database: RENAME TABLE old_db.table TO new_db.table; You will need to adjust the permissions after that. For scripting in a shell, you can use either of the following: mysql -u username -ppassword old_db -sNe ‘show … Read more