DDL
數據庫模式定義語言DDL(Data Definition Language),是用于描述數據庫中要存儲的現實世界實體的語言。Easyswoole提供了一個DDL庫,方便用戶用于定義一個數據庫表結構。
組件要求
- easyswoole / spl:^ 1.2
安裝方法
composer require easyswoole/ddl
倉庫地址
基本使用
創建表(CreateTable)
use EasySwoole\DDL\Blueprint\Create\Table as CreateTable;
use EasySwoole\DDL\DDLBuilder;
use EasySwoole\DDL\Enum\Character;
use EasySwoole\DDL\Enum\Engine;
$scoreSql = DDLBuilder::create('score', function (CreateTable $table) {
$table->setIfNotExists()->setTableComment('成績表'); //設置表名稱
$table->setTableCharset(Character::UTF8MB4_GENERAL_CI); //設置表字符集
$table->setTableEngine(Engine::INNODB); //設置表引擎
$table->int('id')->setIsUnsigned()->setIsAutoIncrement()->setIsPrimaryKey()->setColumnComment('自增ID');
$table->int('stu_id')->setIsUnsigned()->setColumnComment('學生id');
$table->int('course_id')->setIsUnsigned()->setZeroFill()->setColumnComment('課程id');
$table->float('score', 3, 1)->setColumnComment('成績');
$table->int('created_at', 10)->setColumnComment('創建時間');
$table->foreign(null,'stu_id','student','stu_id')
->setOnDelete(Foreign::CASCADE)->setOnUpdate(Foreign::CASCADE);
});
echo $scoreSql;
//結果如下:
CREATE TABLE IF NOT EXISTS `score` (
`id` int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
`stu_id` int UNSIGNED NOT NULL COMMENT '學生id',
`course_id` int UNSIGNED ZEROFILL NOT NULL COMMENT '課程id',
`score` float(3,1) NOT NULL COMMENT '成績',
`created_at` int(10) NOT NULL COMMENT '創建時間',
FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB DEFAULT COLLATE = 'utf8mb4_general_ci' COMMENT = '成績表';
修改表(AlterTable)
use EasySwoole\DDL\Blueprint\Alter\Table as AlterTable;
use EasySwoole\DDL\DDLBuilder;
$alterStuScoreSql = DDLBuilder::alter('score', function (AlterTable $table) {
$table->setRenameTable('student_score')->setTableComment('學生成績表');
$table->modifyIndex('ind_score')->normal('ind_score', 'score')->setIndexComment('學生成績--普通索引');
$table->modifyForeign('fk_stu_id')->foreign('fk_stu_id', 'stu_id', 'student_info', 'stu_id');
});
echo $alterStuScoreSql;
//結果如下:
ALTER TABLE `score` RENAME TO `student_score`;
ALTER TABLE `student_score`
COMMENT = '學生成績表',
DROP INDEX `ind_score`,
ADD INDEX `ind_score` (`score`) COMMENT '學生成績--普通索引';
ALTER TABLE `student_score` DROP FOREIGN KEY `fk_stu_id`;
ALTER TABLE `student_score` ADD CONSTRAINT `fk_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `student_info` (`stu_id`);
刪除表(DropTable)
use EasySwoole\DDL\DDLBuilder;
$dropStuScoreSql = DDLBuilder::drop('student_score');
echo $dropStuScoreSql;
//結果如下:
DROP TABLE `student`;