Hash MySQL database schema

Question

I would like to make a hash / signature of MySQL database schema (without data), in order to have a checksum of it, to ensure it is not modified by others.

How can I achieve it ?

Answer 1

You need table checksum as far as I understand your question:

checksum table `table`

so, just make a checksum of an empty table, I guess

Answer 2

SELECT HEX( CRC32 ( SUM( CRC32( CONCAT(
   COALESCE(TABLE_NAME,''),
   COALESCE(COLUMN_NAME,''),
   COALESCE(ORDINAL_POSITION,''),
   COALESCE(COLUMN_DEFAULT,''),
   COALESCE(IS_NULLABLE,''),
   COALESCE(DATA_TYPE,''),
   COALESCE(CHARACTER_MAXIMUM_LENGTH,''),
   COALESCE(CHARACTER_OCTET_LENGTH,''),
   COALESCE(NUMERIC_PRECISION,''),
   COALESCE(NUMERIC_SCALE,''),
   COALESCE(CHARACTER_SET_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLLATION_NAME,''),
   COALESCE(COLUMN_TYPE,''),
   COALESCE(COLUMN_KEY,'')
  ) ) ) ) ) AS 'hash'
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'database_name';