Question 20478 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL math on text in select list should fail

How can I make MySQL throw an error (not simply issue a warning) when I try and perform a mathematical operation with text in the SELECT list. For example:

SELECT 'foo' * 'bar';

I want that query to fail.

Tags:

2

Answers


  1. 0

    You could define a function like this:

    CREATE DEFINER=`root`@`localhost` FUNCTION `multiply`(a float, b float) RETURNS float
    NO SQL
BEGIN
RETURN a*b;
END

    Then select multiply('foo','bar'); will return an error, while select multiply(2,3); still returns the correct answer.

    But the downside is that you need to use this function in stead of the normal way of multiplying.

    Login or Signup to reply.
  2. 0

    For data altering statements (INSERT/UPDATE/etc), these types of operations will already produce an error, assuming you have the STRICT_TRANS_TABLES sql_mode enabled, which has been the default since mariadb 10.2.4 and mysql 5.7.5.

    For non-data altering statements, have your client check if there was a warning; details depend on what client you are using.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search