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

IGV · 27.08.2009 12:50:52

Имеется база данных. Скажу сразу - она составлена неправильно, но отказаться от неё пока нельзя.

Есть таблица table1 с колонками id_client, sw_ip
Вот например запись от туда:

id_client sw_ip
252 192.168.10.11

В колонке switch_ip содержится запись о том, к какому маршрутизатору привязан клиент.

Для каждого маршрутизатора создана своя отдельная таблица, например для маршрутизатора 192.168.10.11 таблица будет называться sw_1011.
В этой таблице тоже есть поле id_client.

Мне необходимо удалить из базы данных записи о клиенте. Хотелось бы сделать это одним запросом. Хотел попробовать использовать динамический sql, чтобы формировать имя таблицы.

SET @tName = CONCAT('sw_', 

                         REPLACE (

                                 SUBSTRING_INDEX(

                                                  (

                                                    SELECT sw_ip

                                                    FROM table2 AS t

                                                    WHERE t.id_client = '252'

                                                   ),

                                                   '.',

                                                    -2                                                     

                                                 ),

                                  ''                                                  

                                 )

                        );

SET @query = 'DELETE sw_table, SW_CL FROM ? AS sw_table LEFT JOIN SW_CL on SW_CL.id_client=\'252\' WHERE sw_table.ID_CL = \'252\'';

PREPARE stmt1 FROM @query;

EXECUTE stmt1 USING @tName;

DEALLOCATE PREPARE stmt1;

Я пытаюсь сделать следующее:
1. выбрать запись из таблицы table2 с полем id_client равным идентификатору клиента (в примере 252) (на выходе 192.168.10.11);
2. взять подстроку (на выходе 10.11);
3. заменить символ '.' на пустой символ '' (на выходе 1011);
4. осуществить конкатенацию со строкой "sw_" (на выходе sw_1011);
Ну а дальше выполнить этот динамический запрос.

В качестве редактора использую MySQL Query Browser. Весь этот код я вставляю в область текста для запроса.

У меня ошибки.
1) mysql не понимает, как мне кажется функцию Replace, хотя я нашел её в списке String Functions
2) Unknown prepared statement handler stmt1.

Подскажите, что я делаю не так. Может я вообще "не в ту степь пошел"? Как это сделать проще?

P.S.
для того чтобы это написать прочитал на форумеВторой пост.
Версия MySQL сервера 5.1

Отредактированно IGV (27.08.2009 12:52:25)

paulus · 27.08.2009 13:24:00

У Вас слишком много табуляций и ентеров — из-за этого Вы путаетесь в параметрах

mysql> select CONCAT('sw_', REPLACE(SUBSTRING_INDEX(( SELECT '192.168.10.11' ), '.', -2), '.', ''));
+---------------------------------------------------------------------------------------+
| CONCAT('sw_', REPLACE(SUBSTRING_INDEX(( SELECT '192.168.10.11' ), '.', -2), '.', '')) |
+---------------------------------------------------------------------------------------+
| sw_1011 |
+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

IGV · 27.08.2009 14:31:08

вот переписал, нашел ошибку. Я для команды Replace забыл указать что заменить, т.е. забыл второй параметр '.' спасибо за хороший совет.

SET @tName = CONCAT('sw_', REPLACE(SUBSTRING_INDEX(( SELECT ip_sw FROM SW_CL AS t WHERE t.id_client = '252' ), '.', -2), '.', ''));

SET @query = 'DELETE sw_table, SW_CL FROM ? AS sw_table LEFT JOIN SW_CL on SW_CL.id_client=\'252\' WHERE sw_table.ID_CL = \'252\'';

PREPARE stmt1 FROM @query;

EXECUTE stmt1 USING @tName;

DEALLOCATE PREPARE stmt1;

Но у меня всё ещё ошибка

Script line: 3 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '? AS sw_table LEFT JOIN SW_CL on SW_CL.id_client='252' WHERE sw_table.ID_CL = '2' at line 1

Я так понимаю проблема с вопросительным знаком - подстановка параметра. Как нужно это правильно записать?

Отредактированно IGV (27.08.2009 14:31:51)

vasya · 27.08.2009 14:39:08

SET @query =  concat( 'DELETE sw_table, SW_CL FROM ',@tName,' AS sw_table LEFT JOIN SW_CL on SW_CL.id_client=\'252\' WHERE sw_table.ID_CL = \'252\'');

PREPARE stmt1 FROM @query;

EXECUTE stmt1;

IGV · 27.08.2009 14:52:15

Спасибо исправил, это прокатило.
Но он почему то удалил только из таблицы SW_CL. В чем может быть проблема?

Отредактированно IGV (27.08.2009 15:13:56)

paulus · 27.08.2009 15:17:44

У Вас ON не связывает таблицы — я бы на месте MySQL вообще обиделся и не отвечал на такие запросы
Перепишите в виде, который понятный и правильный — DELETE a, b FROM a, b WHERE a.id = 252 OR b.id=252.

А зачем Вам это делать одним запросом? Все равно ж хранимая процедура будет?

vasya · 27.08.2009 15:42:21

paulus написал:
У Вас ON не связывает таблицы — я бы на месте MySQL вообще обиделся и не отвечал на такие запросы

А он видимо и обиделся

Ведь строго говоря: The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause.

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

IGV · 28.08.2009 11:29:15

В общем я пишу программу на C#. Я использую connector.net 6.1. Поэтому хотел написать sql-скрипт, который можно было бы отправить и он выполнился бы. Поэтому и писал его. О хранимых процедурах ничего не знаю. Надо будет почитать.
В общем в коде это выглядит вот так

        public void DeleteClient(int clientID)

        {            

             OpenConnection();

             string command = @"SET @tName = CONCAT('sw_', REPLACE(SUBSTRING_INDEX((SELECT ip_sw FROM SW_CL AS t WHERE t.id_client =                                                                                                                      '@idClient'), '.', -2), '.', ''));"

                                       + @"SET @query = CONCAT('DELETE sw_table, SW_CL FROM ', @tName, ' AS sw_table INNER JOIN SW_CL WHERE                                                                           sw_table.ID_CL = SW_CL.id_client AND SW_CL.id_client=\'@idClient\'');"

                                       + @"PREPARE stmt1 FROM @query;"

                                       + @"EXECUTE stmt1;";

            command = command.Replace("@idClient", clientID.ToString());

            var c = new MySqlCommand(command, _connection);

            c.ExecuteNonQuery();

            CloseConnection();

        }

Пока у меня вылетает exception в строке c.ExecuteNonQuery(); (Fatal error encountered during command execution.)
Если подскажите, как это сделать лучше и проще буду очень благодарен.
Не знаю насколько это вопрос для этой темы.

Отредактированно IGV (28.08.2009 11:32:04)

paulus · 28.08.2009 15:54:18

Почитайте про параметры вот тут http://dev.mysql.com/doc/refman/5.1/en/ … orial.html
21.2.4.1.4. Working with Parameters; это куда проще и куда безопаснее в смысле
SQL injection, чем то, что делаете Вы.

IGV · 28.08.2009 16:16:58

прочитал, исправил. Но проблему это не решило.

IGV · 28.08.2009 18:55:27

Значится так, решил всё сделать на Stored Procedures, как вы и посоветовали.
Создал хранимую процедуру, вот её полный текст:

PROCEDURE KLIENTS.deleteClient(IN idClient CHAR(64))

BEGIN

  SET @tName = CONCAT('sw_', REPLACE(SUBSTRING_INDEX((SELECT ip_sw FROM SW_CL AS t WHERE t.id_client = idClient), '.', -2), '.', ''));

  SET @query = CONCAT('DELETE sw_table, SW_CL FROM ', @tName, ' AS sw_table INNER JOIN SW_CL WHERE sw_table.ID_CL = SW_CL.id_client AND SW_CL.id_client=', idClient);     

  PREPARE stmt1 FROM @query;

  EXECUTE stmt1;

END

Пытаюсь выполнить проверить как работает эта процедура.

CALL deleteClient('4971');

 

Но (что не удивительно) ошибка!

Illegal mix of collations (cp1251_bin,IMPLICIT) and (koi8r_bin,IMPLICIT) for operation '='

Что я делаю не так?

Отредактированно IGV (28.08.2009 19:17:45)

paulus · 28.08.2009 19:20:53

Кодировки cp1251 и koi8r не полностью совместимы. Попробуйте сделать базу
в utf8 или непосредственно в cp1251.

IGV · 28.08.2009 19:28:01

Спасибо, помогло. Плюс если вызывать процедуру из программы всё тоже работает. Спасибо БОЛЬШОЕ. Единственное я так и не понял почему если передавать этот скрипт, как обычную SQL команду, выкидывается исключение fatal error?

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

#1 27.08.2009 12:50:52

запрос с параметрически заданным именем таблицы

#2 27.08.2009 13:24:00

Re: запрос с параметрически заданным именем таблицы

#3 27.08.2009 14:31:08

Re: запрос с параметрически заданным именем таблицы

#4 27.08.2009 14:39:08

Re: запрос с параметрически заданным именем таблицы

#5 27.08.2009 14:52:15

Re: запрос с параметрически заданным именем таблицы

#6 27.08.2009 15:17:44

Re: запрос с параметрически заданным именем таблицы

#7 27.08.2009 15:42:21

Re: запрос с параметрически заданным именем таблицы

paulus написал:

#8 28.08.2009 11:29:15

Re: запрос с параметрически заданным именем таблицы

#9 28.08.2009 15:54:18

Re: запрос с параметрически заданным именем таблицы

#10 28.08.2009 16:16:58

Re: запрос с параметрически заданным именем таблицы

#11 28.08.2009 18:55:27

Re: запрос с параметрически заданным именем таблицы

#12 28.08.2009 19:20:53

Re: запрос с параметрически заданным именем таблицы

#13 28.08.2009 19:28:01

Re: запрос с параметрически заданным именем таблицы

Board footer