How to Create a Sequence in MySQL

How do I create a sequence in MySQL?

Check out this article. I believe it should help you get what you are wanting. If your table already exists, and it has data in it already, the error you are getting may be due to the auto_increment trying to assign a value that already exists for other records.

In short, as others have already mentioned in comments, sequences, as they are thought of and handled in Oracle, do not exist in MySQL. However, you can likely use auto_increment to accomplish what you want.

Without additional details on the specific error, it is difficult to provide more specific help.

UPDATE

CREATE TABLE ORD (
  ORDID INT NOT NULL AUTO_INCREMENT,
  //Rest of table code
  PRIMARY KEY (ordid)
)
AUTO_INCREMENT = 622;

This link is also helpful for describing usage of auto_increment.
Setting the AUTO_INCREMENT value appears to be a table option, and not something that is specified as a column attribute specifically.

Also, per one of the links from above, you can alternatively set the auto increment start value via an alter to your table.

ALTER TABLE ORD AUTO_INCREMENT = 622;

UPDATE 2
Here is a link to a working sqlfiddle example, using auto increment.

I hope this info helps.

MySQL Create View with Sequence Numbers for Each Group

If you are using an older version of MySQL, then you may use a correlated subquery to generate the sequence:

CREATE VIEW yourView AS
SELECT
    DocNo,
    Rev,
    (SELECT COUNT(*) FROM yourTable t2 WHERE t2.DocNo = t1.DocNo AND t2.Rev <= t1.Rev) AS Seq
FROM yourTable t1
ORDER BY
    DocNo,
    Rev;

If you are using MySQL 8+, then use ROW_NUMBER:

CREATE VIEW yourView AS
SELECT DocNo, Rev, ROW_NUMBER() OVER (PARTITION BY DocNo ORDER BY Rev) Seq
FROM yourTable
ORDER BY
    DocNo,
    Rev;

Generate an integer sequence in MySQL

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.

How to generate sequence number in MySQL view?

BTW, I have to use a VIEW to do so, not a SELECT statement. User does
not know how to input SELECT statement, but they know how to click the
view to look at the view.

Technically you want something like this to simulate ranking or a row number..

CREATE VIEW table_view 
AS
 SELECT
  *
  , (@row_number := @row_number + 1) AS row_number 
 FROM 
  table
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
 ORDER BY
  table.column ASC 
CROSS JOIN (SELECT @row_number := 0) AS init_user_var

You can't use this SQL code you will get the error below if you try to create a View with a user variable.

Error Code: 1351
View's SELECT contains a variable or parameter

The SQL code below also makes it possible to generate the row_number.
This assumes that you have a id column what is generated with AUTO_INCREMENT.
But the subquery is a correlated subquery what makes the execution very slow on larger tables because the counting need to be executed on every record.

CREATE VIEW table_view
AS
 SELECT 
  *
  , (SELECT COUNT(*) + 1 FROM table inner WHERE inner.id < outer.id) AS row_number
 FROM 
   table outer

MySQL 8.0+ Only.

MySQL supports window functions so no MySQL´s user variables are needed to simulate ranking or a row number.

CREATE VIEW table_view 
AS
 SELECT
  *
 # Because a SQL table is a unsorted set off data ORDER BY is needed to get stabile ordered results.
  , (ROW_NUMBER() OVER (ORDER BY table.column ASC)) AS row_number
 FROM 
  table

Oracle sequences equivalent in MySQL

I was able to solve this problem by just combining suggestions of @Akina and @RickJames , thank you both for thier support.

create table my_seq(
min_value integer,
Max_value integer,
last_value integer,
increment_by tinyint,
customer_id integer)ENGINE = InnoDB;

Here ENGINE=InnoDB is very important.
In order to make sure there is table level locking while reading, I have modified my app code to:

Auto-Commit=FALSE

Then,

//very import to begin the transaction
begin;
select last_number from my_seq where customer_id=? FOR UPDATE;

Read the result in App.

update my_seq set last_number=last_number+1 where customer_id=?;
commit;

This was generating the unique sequence number even in case of multiple concurrent sessions.

I have faced another problem, that this solution has slowed down other are where I do generate sequence#. I have solved it enabling a row level lock instead of table level lock by indexing customer_id.

ALTER TABLE TABLE_NAME ADD INDEX (customer_id);

Hope this will be help full to others.

How to create a sequence on selected values in MySQL?

Use a user-defined variable that you increment. Also, MySQL uses CONCAT to concatenate strings, not +.

SET @teamid = 123;

SELECT     CASE WHEN ID = @teamid
                THEN NAME
                ELSE CONCAT('Team  - ', @SEQ)
                END AS 'Team Name',
           SUM(TOTAL) AS 'Team Total',
           @SEQ := CASE WHEN ID = @teamid 
                        THEN @SEQ
                        ELSE @SEQ + 1
                   END
FROM       TEAM
CROSS JOIN (SELECT @SEQ := 1) AS vars
GROUP BY   ID;

DEMO

CREATE SEQUENCE IN MYSQL

Assuming you're going to Oracle, just set up the statement, parse, and execute. There's no need to do any binding because you have no bind variables. This is adapted from the PHP documentation of oci_parse:

$conn = oci_connect(your username, your password, your database);
$stid = oci_parse($conn, 'UPDATE tableName SET columnName = seq_test_id.NEXTVAL');
oci_execute($stid);