Mysql – Error in function creating query

functionsMySQLset-returning-functions

There is an error in the following function creating query:

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END

Any idea how to fix?
Can I avoid using ; in the query above? The development framework doesn't allow multiple queries in one call and identifies this one as multiple queries.

EDIT1:
The aim of the function to make each row unique, like an autoincreased primary key. But for some development reason I can't use autoincremented primary keys.

EDIT2:
The error message is usual, that query failed. You have an error in your query.

EDIT3:
I use the function like this way:
SELECT
_increase_num() as rownr,
content
FROM mytable

WORKING SOLUTION:

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1)

Best Answer

Here's a version that does not give a syntax error:

delimiter !!
drop function _increase_num !! 
CREATE FUNCTION _increase_num() 
returns int 
begin 
    return IF(@counter, @counter:=@counter+1, @counter:=1); 
end !!
delimiter ;

A bit of warning though, whenever counter looses its value it will start from 1. Also, I would not put any money on how well this works when the function is called in parallel from several processes.

Edit: ";" has a special meaning in stored procedure language, it separates statements within the procedure. This is why you normally change the statement terminator to something else:

delimiter @

However, for this trivial procedure, we don't need the begin/end block. That is:

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1);

will do.

Related Solutions

Mysql – Query performance with subquery and IN clause

Refactor the query as follows:

SELECT
    readings.*
FROM
    (
        SELECT boxsn FROM readings
        WHERE (time >= 1325404800) 
        AND (time < 1326317400) 
        ORDER BY `time` ASC
    ) readings_keys
    LEFT JOIN
    (
        SELECT id AS boxsn FROM boards WHERE siteId = '1'
    ) boards
    USING (boxsn)
    LEFT JOIN readings
    USING (boxsn)
;

Make sure you have the following indexes:

ALTER TABLE boards ADD INDEX siteId_id_ndx (siteId,id);
ALTER TABLE readings ADD INDEX time_boxsn_ndx (time,boxsn);

You can drop the other index

ALTER TABLE readings DROP INDEX boxsn_time_ndx;

You should definitely see a dramatic improvement in performance as the tables grow.

In your case,

The first EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a list of value in memory
The second EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a table.

UPDATE 2012-01-12 14:03 EDT

I refactored it again to make sure the readings keys and boards keys are combined correctly before retrieving the data from the readings table:

SELECT 
    readings.* 
FROM 
    ( 
        SELECT A.* FROM
        (
            SELECT boxsn FROM readings 
            WHERE (time >= 1325404800)  
            AND (time < 1326317400)  
            ORDER BY `time` ASC
        ) A
        LEFT JOIN
        (
            SELECT id AS boxsn
            FROM boards
            WHERE siteId = '1'
        ) B
        USING (boxsn)
        WHERE B.boxsn IS NOT NULL
    ) readings_keys 
    LEFT JOIN readings 
    USING (boxsn) 
;

MySQL security group view permission query

SELECT DISTINCT record.id AS id, details from record 
LEFT JOIN record_group ON record.id = record_group.record_id
WHERE record_group.group_id IN (SELECT group_id from user_group WHERE user_id = 2)

http://sqlfiddle.com/#!9/e40ee6/12

Best Answer

Related Solutions

Mysql – Query performance with subquery and IN clause

UPDATE 2012-01-12 14:03 EDT

MySQL security group view permission query

Related Question