Performed manually there are some return: 1364 - Field 'shop_id' doesn 't have a default value,
Problem is that this process there is a table by a connecting two attached list of data according to filter out, and then inserted into a new table,
This process of taking if directly on the command line is normal, probably around data is 230 w, table data is about more than 200 million points,
It performs for the error taken out of the shop_id is unlikely to be of no value, I execute a stored procedure alone the SELECT operation, and then press shop_id sort has value,
Does anyone know why this is? This process is used for two days now have not be properly carried out, the other data probably around 150 w as well as normal execution, now 200 w is executed when not bottom go to,
The stored procedure code:
BEGIN
/*
* * Step 1
Name: * * Temp
No
* * parameters:*/
DECLARE
# a date DEFAULT DATE_SUB (CURDATE (), the INTERVAL 1 DAY); # "2017-11-12"
The date a DEFAULT "2018-11-12";
DECLARE staffds INT the DEFAULT 18000; - the default on the online time more than 5 hours is the shop assistant
DECLARE outshops INT the DEFAULT 0; - the default on the day of less than 60 seconds long online is the outside flow
WHILE
A. & lt; CURDATE () DO
INSERT INTO shuibei_lbs. LBS_MessData (TransactionDate, shop_id, ApMac ClientMac, ClientDeviceBrand, EnterTime, Exittime, Inshop, DurationSecond) SELECT
The DATE (A.E nterTime) AS TransactionDate,
# B.r emarks,
B.s hop_id,
Upper (A.E nterSensorMac) AS ApMac,
Upper (A.C lientMac),
"" AS ClientDeviceBrand,
A.E nterTime,
A.E xitTime,
CASE
The WHEN A.R ssi & gt;=B.d evice_rssi
THEN
1 the ELSE 0
END,
TIME_TO_SEC (TIMEDIFF (A.E xitTime, A.E nterTime)) AS DurationBySecond
The FROM
Shuibei_lbs. LBS_CustomerRegionEvent A,
Shuibei_lbs. Shop_device B
WHERE
DATE_FORMAT (A.E nterTime, "Y - m - % d % %")=a
AND upper (A.E nterSensorMac)=B.d evice_mac
# AND A.R ssi & gt;=B.d evice_limit_rssi
# and TIME_TO_SEC (TIMEDIFF (A.E xitTime, A.E nterTime)) & gt; Outshops
The ORDER BY
EnterTime;
- update your equipment brand
The UPDATE shuibei_lbs. LBS_MessData A,
Shuibei_lbs. Oui B
The SET A.C lientDeviceBrand=LEFT (B.Com panyName, 200)
WHERE
The upper (LEFT (A.C lientMac, 8))=B.M AC
AND TransactionDate=a;
- select staff list
INSERT INTO shuibei_lbs. StaffList (ClientMac)
SELECT
Distinct TempT. ClientMac
The from (SELECT TransactionDate, shop_id, ClientMac, sum (DurationSecond) DS
The FROM shuibei_lbs. LBS_MessData
Where TransactionDate=a
Group by TransactionDate, shop_id, ClientMac
The order by TransactionDate, shop_id, ClientMac) TempT
WHERE TempT. DS & gt;=staffds
And TempT. ClientMac not in (select distinct ClientMac from shuibei_lbs, StaffList);
SET a=DATE_ADD (a, the INTERVAL 1 DAY);
END WHILE;
END
CodePudding user response:
See the stored procedure about three, insert data, update data, then insert the data;Suggest three SQL statements, performed manually view which SQL error, according to your description should be the first SQL;
You check the select * from b where shop_is null; See if really free value
If there is no suggest you SELECT statement of inserted separately, don't see with the naked eye, add a condition where shop_id is null.
See if can really produce a null value,
If have ruled out, just to see the others under the 1364 experience, it this way:
Modify SQL_MODE
But it is not recommended, you are inserted the non-standard values in the table, will report the error,
See SHOP_ID field length,
And then the SELECT statement generated SHOP_ID you LENGTH, if someone manually input error, cause the word through long insert,
From SHOP_id this column data, suggest or find non-standard data,