Custom auto-increment column

Question

I am making a C# project in which I need help to generate and insert fields like below on MySQL database.

161013001

Where:
16 is Year,
10 is Month,
13 is day
and 001 is auto-increment numbers that reset each days.

Eg.

please guide me how to make this that ID reset each day and start from 1 after every day.

same Question has provided https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql — vijayvicks, Dec 26 '17 at 08:22
It seems best to me to do it slightly differently. Just put a date and an id in your database. You can count the id by doing a select on the amount of records added today plus one and the date is just todays date. Your primary key is then the date plus the id. When you read the data from the database you cenvert it to the form you desire: 161013001. This way if you want to slightly change the form later, you don't have to adjust your database. — Lavandysh, Dec 26 '17 at 08:24
In Sql server I would solve this kind of thing using a computed column. I have almost no experience with MySql but I would recommend the same approach. Use the database's built it auto-increment property on an int column as your primary key, save the record's create date on a date column, calculate your value format based on these two columns. — Zohar Peled, Dec 26 '17 at 08:44
@ZoharPeled, Unfortunately, you can't make a computed column that references an auto-increment column in MySQL. — Bill Karwin, Dec 26 '17 at 08:48
I would adopt the strategy proposed by Lavandysh. Essentially, you're confusing data display with data storage and retrieval. — Strawberry, Dec 26 '17 at 08:51
@BillKarwin well, that's just one more reason for me to not like this database :-) — Zohar Peled, Dec 26 '17 at 08:51
@Lavandysh Didn't see your comment before posing mine. seems like we offer basically the same solution. — Zohar Peled, Dec 26 '17 at 08:53
@Zohar Peled Yes, but your approach of using auto increment is better then my use of count. — Lavandysh, Dec 26 '17 at 09:06
i make a table in which ContactId is Primary Key and it is auto incremented by 1 now i want that i should be star from 1 after every new day!!! — Syed Raza Sherazi, Dec 26 '17 at 10:16
Can you give me a starting point? It is a lot to code if I don't know what you have already. How do you connect with the database, do you use the entity framework? Do you already have creation scripts for your database, ... — Lavandysh, Dec 26 '17 at 13:03
using System.Data.SqlClient; SqlConnection sqlCon = new SqlConnection(@"Data Source=.;Initial Catalog=ArmyDB;Integrated Security=True"); and iam using stored procedures to save the data into DB.. — Syed Raza Sherazi, Dec 26 '17 at 18:14
@I'm a little short on time right now, if it still isn't done by the time I have time I'll do it. — Lavandysh, Dec 29 '17 at 08:21
@Syed Raza Sherazi do you solving this problem..... if solve then provide you solution as a answer ......... it will be helping others like me........ — Istiaque Hossain, Jul 01 '19 at 10:02

Khairul Alam · Answer 1 · 2019-07-01T11:36:08.247

Here both MySQL & SQL Server Implementation are added,

MySQL:

DROP TEMPORARY TABLE TempDate;
CREATE TEMPORARY TABLE TempDate(
Id VARCHAR(50),
Comments  VARCHAR(50)
);
INSERT INTO TempDate(Id,Comments)
SELECT '190630-001', '1'
UNION 
SELECT '190630-002', '2'
UNION 
SELECT '190701-001', '1'
UNION 
SELECT '190701-002', '2'
UNION 
SELECT '190701-003', '3';

SET @v_ToDay = '';
SET @v_ToDay = (SELECT date_format(current_date(),'%y%m%d'));
SET @v_TotalByDay  = '' ;

SET @v_TotalByDay =(
SELECT CONCAT('000',CAST(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1)+1 END as CHAR))
FROM TempDate
WHERE LEFT(Id,6) = @v_ToDay);

SELECT CONCAT(@v_ToDay, '-', CASE WHEN CHAR_LENGTH(RTRIM(@v_TotalByDay)) > 3 THEN RIGHT(@v_TotalByDay,3) ELSE @v_TotalByDay END) as NewIdColumn

SQL Server:

DECLARE @TempDate TABLE(
Id NVARCHAR(50),
Comments  NVARCHAR(MAX)
)

INSERT INTO @TempDate(Id,Comments)
SELECT '190630-001', '1'
UNION 
SELECT '190630-002', '2'
UNION 
SELECT '190701-001', '1'
UNION 
SELECT '190701-002', '2'
UNION 
SELECT '190701-003', '3'

DECLARE @ToDay NVARCHAR(20) = (SELECT  CONVERT(NVARCHAR(6), GETDATE(), 12))
DECLARE @TotalByDay NVARCHAR(20) = '' 

SELECT @TotalByDay = '000' + CAST(CASE WHEN COUNT(1) = 0 THEN 1 ELSE COUNT(1)+1 END as NVARCHAR(20) )
FROM @TempDate
WHERE LEFT(Id,6) = @ToDay

SELECT @ToDay + '-' + CASE WHEN LEN(@TotalByDay) > 3 THEN RIGHT(@TotalByDay,3) ELSE @TotalByDay END as NewIdColumn

i also facing this type of problem but i am using Mysql ................. your solution may help me to solve my problem.. i will inform later about my problem solving ...... — Istiaque Hossain, Jul 01 '19 at 09:53

Istiaque Hossain · Answer 2 · 2019-07-02T08:27:33.767

my situation is little more different then this question ....

i have a legacy database (it's not operational only use for reporting purpose..)

in this DB transaction table was a auto increment tranx id column. like 1, 2, 3 ...... but now our new report need meaningful tranx id (yyMMDD<count of that day>) like this question. so actually i need a select query to solve this problem.

with the help of @Khairul 's logic i solve my problem ....

i share my solution for other's help....

  SELECT
trnx_id, account_id, pay_amount,counter_id, trantime, trandate
FROM(
SELECT 
    @id:=IF(@prev != t.trandate, @rownum:=1, @rownum:=@rownum+1)
    ,@prev:=t.trandate 
    ,CONCAT(
        SUBSTR(YEAR(t.`trandate`),3) -- year
        ,IF(LENGTH(MONTH(t.`trandate`))=1,CONCAT('0',MONTH(t.`trandate`)),MONTH(t.`trandate`)) -- month
        ,IF(LENGTH(DAY(t.`trandate`))=1,CONCAT('0',DAY(t.`trandate`)),DAY(t.`trandate`)) -- day 
        ,IF(LENGTH(@id)=1,CONCAT('000',@id),IF(LENGTH(@id)=2,CONCAT('00',@id),IF(LENGTH(@id)=3,CONCAT('0',@id),@id))) -- count 
    ) AS trnx_id 
    ,t.* 
FROM tax_info t ORDER BY t.`trandate`, t.`trantime`


) AS te

and my query result is ..........

After solving my problem i try to solve this question ....... for this i use a trigger for input auto increment custom column ... my code is below , here my payment column has a custom tranx id ....

DELIMITER $$ 

DROP TRIGGER  tranxidGeneration$$

CREATE 
    TRIGGER tranxidGeneration BEFORE INSERT ON payment 
    FOR EACH ROW BEGIN 

    DECLARE v_tranx_id_on INT; 

    -- count total row of that day
    select IFNULL(COUNT(tranx_id),0)+1 Into v_tranx_id_on from payment where SUBSTR(tranx_id,1,6) = DATE_FORMAT(NOW(), "%y%m%d");

    -- set custom generate id into tranx_id column 
    SET NEW.tranx_id := CONCAT(DATE_FORMAT(NOW(), "%y%m%d"),LPAD(v_tranx_id_on,4,0)) ; 

    END;
$$ 
DELIMITER ;

Custom auto-increment column

2 Answers2