CREATE TABLE sch (
id INT(10) PRIMARY KEY NOT NULL UNIQUE,
NAME VARCHAR(50) NOT NULL,
glass VARCHAR(50) NOT NULL
);
INSERT INTO sch VALUES
( 1,'xiaoming','glass 1'),
( 2,'xiaojun','glass 2');
创建一个可以统计表格内记录条数的存储函数 ,函数名为count_sch()
# 写函数当中如果报如下错误,没有则忽略
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
# 解决办法
mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE FUNCTION count_sch()
-> RETURNS int
-> BEGIN
-> DECLARE c INT DEFAULT 0;
-> SELECT COUNT(*) INTO c FROM sch;
-> RETURN c;
-> END $ Query OK, 0 rows affected (0.00 sec)
mysql> \d ;
mysql> select count_sch();
+-------------+
| count_sch() |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)