9raxdev/mysql-struct-sync

There is no license information available for the latest version (1.0.3) of this package.

可用于帮助开发人员比较两个数据库之间的差异(表,列,约束,事件,函数,存储过程,触发器,视图),并生成更新语句。Can be used to synchronize database structures, compare differences(table,column,constraints,events,functions,procedures,triggers,views) between databases and generating queries.

Maintainers

Details

github.com/DDZH-DEV/mysql-struct-sync

Source

Issues

Installs: 103

Dependents: 0

Suggesters: 0

Security: 0

Stars: 20

Watchers: 1

Forks: 6

Open Issues: 0

pkg:composer/9raxdev/mysql-struct-sync

1.0.3 2025-10-17 05:13 UTC

Requires

  • php: >=5.4.0
  • ext-mysqli: *

Unknown License 474fd3ba3b601e2b6742a43f1c7480a8e9a9b97c

  • DDZH-DEV <9rax.dev.woop@gmail.com>

This package is auto-updated.

Last update: 2025-10-17 05:15:01 UTC

README

可用于帮助开发人员比较两个数据库之间的差异(表,列,约束,事件,函数,存储过程,触发器,视图),并生成更新语句。 Can be used to synchronize database structures, compare differences(table,column,constraints,events,functions,procedures,triggers,views) between databases and generating queries.

Features

  • Handle Create Tables
  • Handle Alter Tables
  • Handle Drop table Queries
  • Handle constraints (PK,FK,index, ... etc)
  • Handle events
  • Handle functions
  • Handle procedures
  • Handle triggers
  • Handle views

Installation 安装方式

to install this library using composer:

composer require ddzh-dev/mysql-struct-sync

New Features in V2

This library has been significantly updated to support a more robust and efficient workflow for comparing database schemas, especially for database version management systems.

  • Offline Snapshot Comparison: Instead of comparing two live databases, you can now compare a live database against a pre-generated "structure snapshot". This eliminates the need for temporary databases and makes comparisons much faster.
  • Atomic ALTER Statements: The library now generates a single, atomic ALTER TABLE statement for all modifications to a single table. This prevents errors related to operation order (e.g., dropping a primary key before modifying an auto-increment column) and ensures safer migrations.

New API Methods

  • MysqlStructSync::fetchStructureArray(array $db_conf): A static method to fetch the schema of a database as a PHP array (a "snapshot").
  • $compare->getGroupedDiffSql(): Returns an array of SQL statements, with all ALTER queries for the same table grouped into a single query. This is the recommended way to get the difference script.

Attention 注意

1.无法识别rename字段,更改数据库字段名称在代码中的体现为:先删除命名前的字段,再增加命名后的字段。The rename field is not recognized. Changing the database field name in the code is as follows: first delete the field before the name, and then add the named field.

2.advanceDiff()必须基于baseDiff()前提下,因为储存过程,触发器,函数等特性都依赖数据表。advanceDiff() must be based on baseDiff(), because procedures, triggers, functions, etc. depend on the tables.

Demo

Standard Usage: Comparing Two Live Databases

<?php
include __DIR__.'/MysqlStructSync.php';

$source_db_config=['host'=>'127.0.0.1','username'=>'root','passwd'=>'root','dbname'=>'test_old','port'=>3306];
$target_db_config=['host'=>'127.0.0.1','username'=>'root','passwd'=>'root','dbname'=>'test_new','port'=>3306];

// Compare source_db_config against target_db_config
$compare=new \DDZH\MysqlStructSync($source_db_config, $target_db_config);

$compare->removeAutoIncrement();

$compare->baseDiff(); // Compare tables, columns, constraints
$compare->advanceDiff(); // Compare views, triggers, events, etc.

// Get the atomic SQL script (Recommended)
$diff_sql_array = $compare->getGroupedDiffSql();
$diff_sql_script = implode("\n", $diff_sql_array);
print_r($diff_sql_script);

Advanced Usage: Comparing a Live Database to a Snapshot

This is the recommended approach for building a database schema versioning system.

<?php
include __DIR__.'/MysqlStructSync.php';

$production_db_config = ['host'=>'...'];
$development_db_config = ['host'=>'...'];

// Step 1: In your development/build process, create a structure snapshot from the target schema.
$target_structure_snapshot = \DDZH\MysqlStructSync::fetchStructureArray($development_db_config);
// Serialize and store this snapshot string (e.g., in your versions table in the database).
$snapshot_string = json_encode($target_structure_snapshot);


// Step 2: In your deployment/migration process, compare the live database against the loaded snapshot.
$loaded_snapshot = json_decode($snapshot_string, true);
$compare = new \DDZH\MysqlStructSync($production_db_config, $loaded_snapshot);

$compare->removeAutoIncrement();
$compare->baseDiff();
// Note: advanceDiff() does not currently support snapshot comparison.

// Get the atomic SQL script to upgrade the production_db
$diff_sql_array = $compare->getGroupedDiffSql();
$diff_sql_script = implode("\n", $diff_sql_array);
echo $diff_sql_script;

Future Improvements

  • Add support for column ordering (AFTER clause).
  • Add support for advanced objects (views, triggers) in snapshot comparison mode.
  • Replace die() with exceptions for better error handling in library mode.

manuallySelectUpdates