SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 25.01.2008 03:54:16

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Об одной особенности хранимых процедур в MySQL.

Часто возникает необходимость передавать имя таблицы или колонки в виде параметра процедуры. Однако напрямую это сделать нельзя. Если в качестве имени таблицы, столбца и т.п. использовать переменную (неважно локальную или входной параметр), то сервер при выполнении запроса использует имя переменной, а не её значение.
Для иллюстрации напишем простейшую процедуру, которая считает количество уникальных значений определенного столбца.

DROP PROCEDURE IF EXISTS my_proc;
delimiter//
CREATE PROCEDURE my_proc(table_name CHAR(64), column_name char(64))
BEGIN
SELECT count(DISTINCT column_name) FROM table_name;
END;
//

Все примеры будут выполняться на примере учебной базы world, которую Вы можете скачать с сайта MySQL (http://dev.mysql.com/doc/)
Пусть мы хотим узнать количество округов. Выполнение нашей процедуры:
CALL my_proc('City', 'District')//
Приведет к попытке посчитать уникальные значения столбца column_name из таблицы table_name, которой скорее всего не существует, что приведет к ошибке. Те данные которые мы передали в качестве параметров использованы не будут.

Преодолеть данное затруднение можно за счет использования пользовательских переменных и подготовленных выражений.

Пользовательские переменные (user variable).
Пользовательские переменные записываются как @var_name, действуют в течении сессии, не чувствительны к регистру.
Могут быть определены следующими способами:
1) В выражениях SET с помощью операторов ' ='  или ' := '
test >set @v1 = 'one', @v2 := 'two';
Query OK, 0 rows affected (0.00 sec)

test >select @v1, @v2;
+------+------+
| @v1  | @v2  |
+------+------+
| one  | two  |
+------+------+
1 row in set (0.00 sec)
2) В выражениях select с помощью оператора ' := '
test >select @v1 := 55;
+-----------+
| @v1 := 55 |
+-----------+
|        55 |
+-----------+
3) С помощью выражений select .. from .. into  (эквивалентно select .. into .. from)
test >select col_int into @av from aa limit 1;
Query OK, 1 row affected (0.00 sec)

test >select @aV;
+------+
| @aV  |
+------+
| 1990 |
+------+
1 row in set (0.00 sec)

Если переменная не была определена явно, то принимает NULL значение.
test >select @x;
+------+
| @x   |
+------+
| NULL |
+------+

Пользовательской переменной может быть присвоен результат выполнения функции. Например:
world >SET @var = CONCAT('SELECT count(DISTINCT ', 'District', ') FROM ', 'City');
Query OK, 0 rows affected (0.00 sec)

world >SELECT @var;
+-------------------------------------------+
| @var                                      |
+-------------------------------------------+
| SELECT count(DISTINCT District) FROM City |
+-------------------------------------------+
1 row in set (0.00 sec)


Подготовленные выражения (prepared statement)
Подготовленные выражения (prepared statement) отличаются от обычных запросов тем, что не выполняются по завершению парсинга выражения, а хранятся на сервере. Выполняются в момент их вызова  командой "EXECUTE имя_выражения" (возможен многократный вызов). Обычно применяются, когда необходимо выполнить ряд одинаковых (или незначительно отличающихся) запросов. Например, многократно выполняем запрос указывая различную временную величину. Вследствии того, что хранятся на сервере - уменьшают трафик и время на парсинг (выражение разбирается один раз). 
Существуют только в течении сессии. Если создается новая с уже существующим именем, старая автоматически удаляется (даже если новая содержит ошибку и не будет создана).

Имеет следующий синтаксис:
PREPARE имя_выражения_какое_хотите FROM какой_нибудь_запрос;
EXECUTE имя_выражения_какое_хотите;

Запрос можно вписать сразу, например:
world >PREPARE zxc FROM 'SELECT count(DISTINCT District) FROM City';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

world >EXECUTE zxc;
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.03 sec)

Или сначала сделать переменную и потом приготовить выражение из нее:
world >SET @query = 'SELECT count(DISTINCT District) FROM City';
Query OK, 0 rows affected (0.00 sec)

world >PREPARE zxc FROM @query;
Query OK, 0 rows affected (0.38 sec)
Statement prepared

world >EXECUTE zxc;
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.38 sec)
Это очень хорошее свойство, так как позволяет программисту формировать запрос динамически.

Кроме того, можно выполнять запросы с различными параметрами.
PREPARE имя_выражения FROM 'SELECT count(*) FROM table_name WHERE column1= ?';
SET @var = '54';
EXECUTE имя_выражения USING @var;
Вернет количество строк в таблице table_name, у которых column1=54.



Применительно к нашему случаю решение будет выглядеть след. образом:

DROP PROCEDURE IF EXISTS my_proc//
CREATE PROCEDURE my_proc(table_name CHAR(64), column_name CHAR(64))
BEGIN
SET @var = CONCAT('SELECT count(DISTINCT ', column_name, ') FROM ', table_name);
PREPARE zxc FROM @var;
EXECUTE zxc;
END;
//

world >call my_proc('City', 'District')//
+--------------------------+
| count(DISTINCT District) |
+--------------------------+
|                     1367 |
+--------------------------+
1 row in set (0.00 sec)

Отредактированно vasya (02.02.2008 04:17:49)

Неактивен

 

#2 25.01.2008 07:02:38

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Ага, про это даже оригинальная статья есть (на семинаре в свое время с этим намучились):
http://dev.mysql.com/tech-resources/art … dproc.html

Неактивен

 

#3 25.01.2008 17:52:34

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Об одной особенности хранимых процедур в MySQL.

А как вам вот такой примерчик с перекрытием названий? smile

Разумеется, не на ту же тему, просто подумал, что это куда больше особенность
хранимых процедур smile

Код:

mysql> delimiter ;;
mysql> create table test (f int);;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values (1), (2), (3);;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create procedure proc (f int)
    -> select f from test;;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc (1);;
+------+
| f    |
+------+
|    1 | 
|    1 | 
|    1 | 
+------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Можете придумать, как вывести столбец f из test?

Неактивен

 

#4 25.01.2008 18:11:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Об одной особенности хранимых процедур в MySQL.

Код:

mysql> create procedure proc (f int) select test.f from test;;
Query OK, 0 rows affected (0.00 sec)

mysql> call proc (1);;
+------+
| f    |
+------+
|    1 | 
|    2 | 
|    3 | 
+------+
3 rows in set (0.00 sec)

Неактивен

 

#5 25.01.2008 22:35:15

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Об одной особенности хранимых процедур в MySQL.

paulus написал:

А как вам вот такой примерчик с перекрытием названий? smile

Разумеется, не на ту же тему, просто подумал, что это куда больше особенность
хранимых процедур smile

Отличный пример. Я как-то забыл о такой особенности.

Общее правило здесь такое:
1)Локальные переменные имеют приоритет над параметрами процедуры и именами столбцов
2)Локальные переменные во внутренних блоках имеют приоритет над определенными во внешних
3)Параметры процедуры имеют приоритет над именами столбцов.

Проиллюстрировать можно, удаляя комментарии в следующей процедуре

Код:

create table f (f int);
insert into f values (1), (2), (3);

delimiter //
create procedure proc (f int)
begin
  # declare f int default 0;
  select f from f;
  begin
   /* declare f int default 1;*/
    select f from f;
  end;
end;
//

call proc(5)//

P.S. MySQL поддерживает три типа комментариев:
1) От символа '#' и до конца строки
2) От последовательности символов '-- ' и до конца строки. Наличие пробела после двух дефисов обязательно.
3) Комментарий начинается с '/*' и продолжается до '*/'. (многострочный комментарий)

Неактивен

 

#6 27.01.2008 03:06:13

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Можете придумать, как вывести столбец f из test?

Сколько ни бились (не на одном семинаре), придумали только через подготовленные выражения:

DROP PROCEDURE IF EXISTS p;
DELIMITER $$
CREATE PROCEDURE p (IN n VARCHAR(64))
BEGIN
  SET @q = CONCAT('SELECT ', n, ' FROM test');
  PREPARE st FROM @q;
  EXECUTE st;
END
$$
DELIMITER ;

CALL p('i');

Неактивен

 

#7 27.01.2008 09:27:45

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Об одной особенности хранимых процедур в MySQL.

Несколько не удачное решение. В примере Паулюса параметр процедуры не передает имя столбца. Речь идет о перекрытие названий, а параметр нужен для каких-то других действий в процедуре.
А вот на решение rgbeastа нужно обратить внимание - все гениальное просто.

Неактивен

 

#8 27.01.2008 12:42:34

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Несколько не удачное решение. В примере Паулюса параметр процедуры не передает имя столбца. Речь идет о перекрытие названий, а параметр нужен для каких-то других действий в процедуре.

Что-то  я не понял.. Я думал, что наоборот хочется процедуре передать имя столбца..
В чем проблема-то? Ведь имена переменных можно выбирать как угодно при создании процедуры, зачем их перекрывать с именами столбцов.

А вот на решение rgbeastа нужно обратить внимание - все гениальное просто.

Да, оно неплохое, но нужно помнить номер столбца, что неуниверсально.

Неактивен

 

#9 27.01.2008 13:20:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Об одной особенности хранимых процедур в MySQL.

> В чем проблема-то? Ведь имена переменных можно выбирать как угодно при создании процедуры, зачем их перекрывать с именами столбцов.
Разве, что из любви к исскуству.

> Да, оно неплохое, но нужно помнить номер столбца, что неуниверсально.
Не понял где здесь номер столбца. f - это имя столбца. Указывать номер столца в выражении select можно только в части order by или group by.

Неактивен

 

#10 27.01.2008 13:47:57

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Тогда я не понимаю, в чем прикол вызывать процедуру с параметром, который потом внутри нее нигде не встречается...

Неактивен

 

#11 27.01.2008 14:32:36

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Об одной особенности хранимых процедур в MySQL.

Хочешь реалистичный пример? Пожалуйста. Пусть у нас фирма занимается... ну, не знаю,
пусть мы печем хлеб. У нас есть табличка заказов:

CREATE TABLE orders (id INT KEY AUTO_INCREMENT, company_id INT NOT NULL, quantity INT NOT NULL);

Мы делаем процедуру добавления заказа в эту табличку:

CREATE PROCEDURE process_order (IN company_id INT, IN quantity INT) ...

Внутри процедуры хочется сделать запрос к orders, чтобы узнать, есть ли такая фирма.

Разумеется, специально никто так же параметр не называет, но бывает так, что случайно
названия совпадают. А иногда и умышленно: процедуры делают одни люди, а таблицы
другие smile

Неактивен

 

#12 27.01.2008 14:43:46

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Ясно.

Ну, вообще это вопрос к разработчикам, почему они решили такой синтаксис сделать, чтобы можно было переменные перепутать со столбцами..
Имхо неудобно конечно (могли бы переменную начинать с какого-нибудь доллара или с чего-нибудь еще.. не понимаю, почему так было не сделать - переменную по виду не отличишь)

Неактивен

 

#13 27.01.2008 15:15:12

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Об одной особенности хранимых процедур в MySQL.

Это стандарт smile

Неактивен

 

#14 27.01.2008 16:04:49

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Ну и зачем было такой стандарт делать?
Есть же php и perl, например. Там знаки доллара. Разве плохо?
Нет, нужно же было геморрой создать...

Неактивен

 

#15 01.02.2008 02:47:09

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Об одной особенности хранимых процедур в MySQL.

А зачем нужны PHP и Perl? Есть хороший язык C, там нету знаков доллара smile

Неактивен

 

#16 01.02.2008 02:50:24

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Об одной особенности хранимых процедур в MySQL.

Зачем нужен C, если есть Assembler, в котором подобной проблемы с видимостью имен переменных нет изначально smile

Неактивен

 

#17 01.02.2008 02:58:38

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: Об одной особенности хранимых процедур в MySQL.

о! Мы нашли предел мечтаний! smile
Зачем нужен ассемблер, если можно писать в машинных кодах сразу и не париться
с буквами в принципе? smile
http://www.ninisworld.com/oddsends/just … mmers.html

Неактивен

 

#18 01.02.2008 13:54:45

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

paulus написал:

А зачем нужны PHP и Perl? Есть хороший язык C, там нету знаков доллара smile

Ну и плохо, что нет. Имхо неудобно (хотя я на нем практически не писал, не знаю - может, так лучше).

Неактивен

 

#19 01.02.2008 13:55:33

LazY
_cмельчак
MySQL Authorized Developer and DBA
Зарегистрирован: 02.04.2007
Сообщений: 845

Re: Об одной особенности хранимых процедур в MySQL.

Давайте еще вспомним язык из пробелов, табуляций и переводов строки.

Неактивен

 

#20 01.02.2008 15:07:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Об одной особенности хранимых процедур в MySQL.

LazY написал:

Давайте еще вспомним язык из пробелов, табуляций и переводов строки.

конечно вспомним - whitespace называется http://compsoc.dur.ac.uk/whitespace/

Неактивен

 

#21 15.04.2008 17:54:28

EugeneTM
Гуру
Зарегистрирован: 11.04.2008
Сообщений: 89

Re: Об одной особенности хранимых процедур в MySQL.

Действительно крайне удобная особенность.
Позволяет из какого нибудь PHP передать в процедуру табличные данные. Например для INSERT'а.
Возник вопрос

mysql> use test;
Database changed
mysql> CREATE TABLE  `test1` (`numfld` int , `strfld` varchar(10));
Query OK, 0 rows affected (0.05 sec)

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`test_ins` $$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE PROCEDURE `test_ins`(ins_val VARCHAR(21000))
    -> BEGIN
    -> SET @var = CONCAT('INSERT INTO test1 VALUES ', ins_val);
    -> PREPARE zxc FROM @var;
    -> EXECUTE zxc;
    ->
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> set @stri="(1, 'Q'),(2,'W'),(3,'E'),(4,'R'),(5,'T')";
Query OK, 0 rows affected (0.02 sec)

mysql> call test_ins(@stri);
Query OK, 5 rows affected (0.03 sec)

mysql> select * from test1;
+--------+--------+
| numfld | strfld |
+--------+--------+
|      1 | Q      |
|      2 | W      |
|      3 | E      |
|      4 | R      |
|      5 | T      |
+--------+--------+
5 rows in set (0.02 sec)

mysql>

Все работает. Но
Если

DELIMITER ;
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`test_ins` $$
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE PROCEDURE `test_ins`(ins_val VARCHAR(64000))
    -> BEGIN
    -> SET @var = CONCAT('INSERT INTO test1 VALUES ', ins_val);
    -> PREPARE zxc FROM @var;
    -> EXECUTE zxc;
    ->
    -> END $$
ERROR 1074 (42000): Column length too big for column '' (max = 21845); use BLOB
or TEXT instead
mysql>
mysql> DELIMITER ;
mysql>

Не очень понятно откуда растет цифра 21845. Все что в мануале нашел

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535. The effective maximum length of a VARCHAR is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

Что определяет max=21845 ?

Неактивен

 

#22 15.04.2008 18:31:11

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: Об одной особенности хранимых процедур в MySQL.

У Вас кодировка UTF8. 65,535 байт в кодировке UTF-8 это 21845 символов, так как каждый UTF-символ занимает в MySQL 3 байта

Неактивен

 

#23 10.08.2012 16:01:36

platedz
Старожил
Зарегистрирован: 27.03.2012
Сообщений: 314

Re: Об одной особенности хранимых процедур в MySQL.

Уважаемые специалисты, будьте добры. Напишите какой-нибудь простой пример с подготовкой зароса и переменной на PHP. Буду крайне признателен.

Я делаю примерно так

mysql_query(" PREPARE cda FROM 'select * from ?' ");
mysql_query("SET @abc = 'table1' ");
$result = mysql_query(" EXECUTE cda @abc");
$fetch = mysql_fetch_array($result);

ругается на fetch_array в первую очередь

Отредактированно platedz (10.08.2012 19:29:19)


Wazzup.su - это сервис объединивший в себе все в одном.

Неактивен

 

#24 10.08.2012 20:42:55

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: Об одной особенности хранимых процедур в MySQL.

Неактивен

 

#25 10.08.2012 22:55:32

platedz
Старожил
Зарегистрирован: 27.03.2012
Сообщений: 314

Re: Об одной особенности хранимых процедур в MySQL.

Спасибо большое за ссылки. По большому счету нужно в одной строке исключить одну колонку из таблицы, чтобы в дальнейшем при обновлениях скрипта не возникало проблем. Беспокоит то, что все запросы идут через mysql и то, что где-то она может быть скажем отключена или еще по каким-либо причинам не работать. Поэтому хотелось получить такой запрос через mysql.

Насколько я понимаю, подготовленные выражения в Php доступны только через mysqli и не доступны через mysql. Поправьте пожалуйста, или подтвердите мои слова.

Буду очень признателен.


Wazzup.su - это сервис объединивший в себе все в одном.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson