Suppose I want to create a "stored function" for MYSQL. Where do I wrote it? Which file?

Question

Of course, I could go into mysql console and type the Function. But what if I want to store it for future use? What do I do?

Answer 1

Most projects have an SQL file to initialize the database from scratch. This way, one can set up the application database by simply running this SQL file. Your CREATE PROCEDURE/FUNCTION query would also go in this file.

Answer 2

There's a good tutorial here:

http://www.databasejournal.com/features/mysql/article.php/3525581/MySQL-Stored-Procedures-Part-1.htm

You need to use stored procedures. Once written, these are stored in the database along with your tables. They can be invoked using the CALL <procedure> statement.

Here's an example procedure that populates table1 with random values:

DELIMITER //
DROP PROCEDURE IF EXISTS autofill//
CREATE PROCEDURE autofill()
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < 20000 DO
        INSERT INTO table1 (size) VALUES (FLOOR((RAND() * 1000)));
        SET i = i + 1;
    END WHILE;
END;
//
DELIMITER ;

Once the procedure has been written, it is called like this:

CALL autofill();