How to add a column in the database where its value is equal to the current year + the table id field?

Asked

Viewed 68 times

1

I have a table called "users" in the database. See below the table structure:

inserir a descrição da imagem aqui

I would like to enter a field called "matricula" varchar (50) there after the "id" field. However I would like this "matricula" field to be auto_increment and Unic. (so far so good, I can do).

The problem is that I would like this "license plate" field to always receive the value (automatically) of: current year + the field "id".

For example, if entering a new user’s id was 741, then its "enroll" field would automatically receive the value of "2020741".

OBS: Remembering that this would be done in an existing table and with data.

OBS2: I know I could do this when entering the data with the programming language (php in my case), but I would like to do this directly with the Mysql.

Thanks for your attention. Thank you !

mysql

  • For the existing data, you will need to do some update, for the new data, ever thought to work with some Rigger?

  • Yes, a Trigger would be a good option. But this would not be to be set in the field creation ?

  • There is the default value for the fields, but as it will be something more dynamic, which will require even a date treatment to get the year, I do not know if it is possible, I have never tried, but it is worth searching, if accept a function for example, you could create this function in Mysql and associate to the default field.

  • Opa, it seems that it is possible to use an expression: https://stackoverflow.com/questions/270309/can-i-use-a-function-for-a-default-value-in-mysql

  • Take a look at this post: https://answall.com/questions/52492/como-personalizar-a-numera%C3%A7%C3%A3o-do-auto-increment-no-mysql

  • I’ll take a look, Daniel and Rodrigo. Return to you if it served me. Thank you !

  • Might not be able to set the default as the current year + the id field ?

Show 2 more comments

1 answer

0

I wouldn’t say that this is a good approach, but it should solve your problem.. that would be the matriculation field’s Insert.

  CREATE TRIGGER `usuarios_before_insert` BEFORE INSERT ON `usuarios` FOR EACH ROW BEGIN
  IF new.matricula IS NULL THEN
    SET new.matricula = (SELECT CONCAT(YEAR(CURDATE()), (SELECT `auto_increment` FROM INFORMATION_SCHEMA.TABLES
        WHERE table_name = 'usuario' ORDER BY `auto_increment` desc LIMIT 1)) AS id)
  END IF;
END

  • As I mentioned in the question, buddy, in that case I would have to do this query in the programming language, and the intention is to do this directly in mysql.

  • I think it’s in the path of an alter table table add column ... bla bla bla ...

  • If I understand now, I believe this Trigger can solve your problem.. during the Insert it will generate the matricula.

  • João, I’ll try to see if I can do it directly in the field. If I can’t, I’ll go to your Rigger. I’ll get back to you. Thank you !

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.