Mysql: four fields of sequence, the sequence only-CodePudding

 
Insert into etcvehiclerecord (userId, vehicleId, obuid, cardid) 
Select distinct o.u serId, o.v ehicleId o.i d obuid, c.i d cardid from 
Obuupload o 
Left the JOIN cardupload c ON o.v ehicleId=c. ehicleId and o.u serId=c.u serId 
Left the JOIN vehicleupload v ON o.v ehicleId=v.i d and o.u serId=v.u serId;

Combination of three tables, queries, query out four fields, only the four fields of sequences, one of the fields as possible is NULL, so the four fields to form joint index,
Historical data, can use this script is found out, but how to increase incremental data?

One way, is the way of the code, first found out all the sequence, then each judge 4 fields sequence,
If the new sequence is all four fields is not empty, and there is no table is new,
If the new sequence is all four fields is not empty, but there is a certain field as a sequence table empty, the existence of a field as an empty sequence before the delete, the new warehouse 4 sequences,
If the new sequence is four fields, part of the field is empty (new), directly into the Treasury,
But the more troublesome, want to ask next everybody directly using SQL script to do?

CodePudding user response:

Changed my way of thinking,
Get these four sequences into the primary key, then update with insert ignore into way,

CodePudding user response:

Insert into etcvehiclerecord (userId, vehicleId, obuid, cardid)
Select distinct o.u serId, o.v ehicleId o.i d obuid, c.i d cardid from
Obuupload o
Left the JOIN cardupload c ON o.v ehicleId=c. ehicleId and o.u serId=c.u serId
Left the JOIN vehicleupload v ON o.v ehicleId=v.i d and o.u serId=v.u serId
Where not the exists (
Select 1 from etcvehiclerecord e where concat_ws (', ', the e.u serId, e.v ehicleId, e.o buid, e.c. with our fabrication: ardid)=concat_ws (', 'o.u serId, o.v ehicleId, o.i d obuid c.i d cardid)
)
Concat_ws fields in it, and if it is null, want to use an empty string, said can't use null, otherwise there may be errors, such as concat_ws (', ', '1', null, '2')='1, 2, concat_ws (',', '1', ', '2')='1, 2', then you insert ignore into also want to see if there are any empty value problems

CodePudding user response:

With concat_ws don't concat because concat if a value is null, the entire result is null, concat_ws, there is no this kind of situation, then concat_ws fields, if you don't want to go to modify the null in the table to an empty string, each field can add ifnull (user_id, "), is the executive may slow down a little