Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to have my auto increment column to increment by step of 3 upon insert.

Can I do something like:

Alter table `mytable` auto_increment = (select max(id) from mytable) + 3;

in a Trigger?

I understand that MySQL has a AUTO_INCREMENT_INCREMENT setting, but since I am on a shared server, I cannot change it.

share|improve this question
    
what are you trying to achieve? –  cha Jun 26 at 4:33
    
I want to have my primary id column to auto increment in step of 3 instead of 1. (i.e.: 1,3,5,etc..) –  user666923 Jun 26 at 4:42

1 Answer 1

up vote 0 down vote accepted

In MySQL you can insert directly into AUTO_INCREMENT Column. So, take this example from MySQL manual:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Let's try this:

TRUNCATE TABLE animals;

CREATE TRIGGER increment_insert BEFORE INSERT ON animals
  FOR EACH ROW 
    SET NEW.id = (SELECT MAX(id) + 2 FROM animals);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Which returns:

| ID |    NAME |
|----|---------|
|  1 |     dog |
|  3 |     cat |
|  5 | penguin |
|  7 |     lax |
|  9 |   whale |
| 11 | ostrich |

(BTW, it is not STEP of 3, it is STEP of 2 you are after)

SQL Fiddle

share|improve this answer
    
thank you so much! SET NEW.id = (SELECT MAX(id) + 2 FROM animals); did the trick! –  user666923 Jun 27 at 3:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.