Sequences

The Sequence class in Weasel.MySql provides sequence-like behavior using a table-based implementation for broad MySQL version compatibility.

How It Works

Since native CREATE SEQUENCE support was only added in MySQL 8.0 (via MariaDB compatibility), Weasel.MySql implements sequences as tables with an AUTO_INCREMENT primary key and a current_value column. This works across all MySQL versions.

Defining a Sequence

var seq = new Sequence("order_seq");
seq.StartWith = 1000;
seq.IncrementBy = 1;

^{snippet source | anchor}

Generating DDL

var migrator = new MySqlMigrator();
var writer = new StringWriter();
seq.WriteCreateStatement(migrator, writer);

^{snippet source | anchor}

This generates:

CREATE TABLE IF NOT EXISTS `public`.`order_seq` (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    current_value BIGINT NOT NULL DEFAULT 1000
);
INSERT IGNORE INTO `public`.`order_seq` (current_value) VALUES (1000);

To drop:

seq.WriteDropStatement(migrator, writer);
// Output: DROP TABLE IF EXISTS `public`.`order_seq`;

^{snippet source | anchor}

Delta Detection

Sequence existence is checked via information_schema.tables:

// Delta detection happens automatically during schema migration.
// The sequence is created if the backing table does not exist.

^{snippet source | anchor}

Usage Pattern

To get the next value from the sequence in application code:

UPDATE `order_seq` SET current_value = LAST_INSERT_ID(current_value + 1);
SELECT LAST_INSERT_ID();