0

I want to setup a filter on mysql server side so the client doesn't need to worry about if the message is valid. e.g , if value>10, accept, else ignore.

One way I think of is to setup a BEFORE INSERT trigger, but I don't know the proper way to drop the invalid value. AFTER INSERT delete works but not an option,I just want the server to skip insert processing on this one. Can anyone give me a hint? Thanks

joe
  • 1,078
  • 2
  • 11
  • 30
  • isn't validation of data like this something you'd do in your application layer? – ADyson Jul 14 '17 at 06:28
  • 1
    A `BEFORE INSERT` trigger can throw an error and abort the statement processing. But the trigger won't cause the INSERT to "ignore" a row. It's not possible for a normal INSERT statement to "filter out" some rows. (Once the BEFORE INSERT trigger completes, the normal INSERT processing is going to continue. There's nothing the trigger can do about that, except throwing an error, or setting the value of some column(s) that will cause the INSERT to fail. – spencer7593 Jul 14 '17 at 06:28
  • As https://stackoverflow.com/questions/32931915/how-to-break-insert-query-before-insert-trigger-mysql suggests, you can set a non-null value to null which will throw an error, but this error may be misleading. – Nigel Ren Jul 14 '17 at 06:28
  • Maybe this helps https://stackoverflow.com/questions/24/throw-an-error-in-a-mysql-trigger – RubioRic Jul 14 '17 at 06:29
  • @spencer7593 I notice that throw error would skip the insert, but I'm afraid this will influence mysql performance while high-speed inserting. Disappointed that mysql trigger is not able to skip a insert process :(. – joe Jul 14 '17 at 06:31
  • @joe: a trigger throwing an error doesn't just "skip" a row. It would cause the entire INSERT statement to be aborted... that affects not just the one row that caused the error, but every row that was inserted. If you are doing singleton inserts (inserting a single row with each INSERT), then aborting the INSERT would cause that row to be skipped. (Contrasted to the more general case, in terms of "high-speed inserting", we wouldn't be running a separate insert statement for each row. Processing RBAR (row by agonizing row) isn't "high-speed".) – spencer7593 Jul 17 '17 at 14:41

0 Answers0