Автор Тема: MySQL замена в строке  (Прочитано 5623 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Оффлайн jx

  • Глобальный модератор
  • Коллежский асессор
  • *****
  • Сообщений: 291
  • Репутация: 16
  • Пол: Мужской
MySQL замена в строке
« : ґХЪРСам 10, 2008, 04:43:53 pm »
всем привет.

подскажите, возможно ли одним запросом сделать такое:

таблица t1 (id INT, s SET ('1', '2', '3', '4', '5'))
id    s
-----------
1   1
2   1,2
3   3,4,5

таблица t2 (id INT, p TINYTEXT)
id    p
--------------------
1   abc
2   def
3   hgi
4   klm
5   nop

нужно одним запрсом заменить в t1.s каждый элемент множества на соответствующий текст из t2.p. чтоб получилось типа:
id    s
-----------
1   abc
2   abc,def
3   hgi,klm,nop

на php это не хочется писать. а sql функция REPLACE не может это сделать.

Оффлайн gigauser

  • Статский советник
  • *****
  • Сообщений: 976
  • Репутация: 20
  • Banned
Re: MySQL замена в строке
« Ответ #1 : ґХЪРСам 10, 2008, 05:06:55 pm »
не могу пока придумать лучшего способа, чем вынести принадлежность в отд. таблицу:
t1(INT, INT):
id   s
-----------------------------
1   1
2   1
2   2
3   3
3   4
3   5
в ней уже заменить s на нужный t2.p и сконкатенировать обратно...
там имхо проще, чем возиться с FIND_IN_SET() и UPDATE
Banned

Оффлайн jx

  • Глобальный модератор
  • Коллежский асессор
  • *****
  • Сообщений: 291
  • Репутация: 16
  • Пол: Мужской
Re: MySQL замена в строке
« Ответ #2 : ґХЪРСам 11, 2008, 08:38:54 pm »
gigauser, спасибо. но это понятно. просто хотелось бы сделать это одним запросом.

т.е. если обычное числовое поле, которое содержит ключ, то вывод строкового значения по этому ключу легко сделать, используя JOIN.

вот и мне хотелось бы сделать тоже самое для поля, которое может содержать "как бы" несколько значений. но, видимо, тут одним запросом не обойтись. или можно это сделать таким исполнением, как посоветовал ты?

Оффлайн gigauser

  • Статский советник
  • *****
  • Сообщений: 976
  • Репутация: 20
  • Banned
Re: MySQL замена в строке
« Ответ #3 : ґХЪРСам 12, 2008, 02:17:52 am »
не, так не получится, мы должны наш t1.s SET разбить по значениям, для этого например можно было бы воспользоваться чем-то типа SELECT t1.s a,  t1.s b FROM  t1 where a &1 OR b &2, но мы не знаем заранее, сколько полей мы должны выбрать. Тут уже надо пользоваться триггерами, в котороых я 0.

Можно например попробовать типа

UPDATE t1 SET s  = (SELECT t2.p FROM t2 WHERE t2.id = t1.s & 1),  (SELECT t2.p FROM t2 WHERE t2.id = t1.s & 2),  (SELECT t2.p FROM t2 WHERE t2.id = t1.s & 4),  (SELECT t2.p FROM t2 WHERE t2.id = t1.s & 8)
т.е. перебираем все поля до последнего возможного, м.б. что несуществующие элементы просто проигнорируются.

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

Оффлайн jx

  • Глобальный модератор
  • Коллежский асессор
  • *****
  • Сообщений: 291
  • Репутация: 16
  • Пол: Мужской
Re: MySQL замена в строке
« Ответ #4 : ґХЪРСам 12, 2008, 09:03:20 am »
Неужели никак нельзя привести таблицу в нормальный вид? Я честно, никогда не видел, чтобы пользовались SET-ом...   

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

если кому надо, вот пример:
Код: (sql) [Выделить]
////////////////////////////////////////
//таблица с данными
////////////////////////////////////////
DROP TABLE IF EXISTS таблица_данных;
CREATE TABLE таблица_данных
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
группа INT UNSIGNED NOT NULL,
значение INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
//заполняем
INSERT INTO таблица_данных VALUES
//{один, два, пять}
(NULL, 1, 1),
(NULL, 1, 2),
(NULL, 1, 5),
//{один, четыре}
(NULL, 2, 4),
(NULL, 2, 1),
//{три}
(NULL, 3, 3),
//{два, три}
(NULL, 4, 3),
(NULL, 4, 8),
(NULL, 4, 2);


////////////////////////////////////////
//таблица с заменами
////////////////////////////////////////
DROP TABLE IF EXISTS таблица_замен;
CREATE TABLE таблица_замен
(
id INT UNSIGNED NOT NULL,
строка TINYTEXT NOT NULL,

PRIMARY KEY (id)
);
//заполняем
INSERT INTO таблица_замен VALUES
(1, 'один'),
(2, 'два'),
(3, 'три'),
(4, 'четыре'),
(5, 'пять');

////////////////////////////////////////
//функция замены
////////////////////////////////////////
DROP FUNCTION IF EXISTS funcReplace;
CREATE FUNCTION funcReplace(nGroup INT)
RETURNS TINYTEXT
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'функция замены'

BEGIN
//разделитель
DECLARE sDelim TINYTEXT DEFAULT ', ';
//результат
DECLARE sResult TINYTEXT DEFAULT '';
//временное значение
DECLARE sTmp TINYTEXT DEFAULT '';
//количество значений в группе
DECLARE nCount INT DEFAULT 0;
//курсор для данных
DECLARE curData CURSOR FOR SELECT таблица_замен.строка FROM таблица_данных LEFT JOIN таблица_замен ON (таблица_данных.значение=таблица_замен.id) WHERE группа=nGroup ORDER BY таблица_данных.значение ASC;
//курсор для количества значений в группе
DECLARE curCount CURSOR FOR SELECT COUNT(*) FROM таблица_данных WHERE группа=nGroup;

//получаем количество значений в группе
OPEN curCount;
FETCH curCount INTO nCount;
CLOSE curCount;

//перебираем данные
OPEN curData;
WHILE nCount > 0 DO
FETCH curData INTO sTmp;

//если строка не пустая, то добавляем её к результату
IF LENGTH(sTmp) > 0 THEN
SET sResult = CONCAT(sResult, sTmp, sDelim);
END IF;

//уменьшаем счётчик
SET nCount = nCount - 1;
END WHILE;
CLOSE curData;

//обрезаем лишний разделитель
IF LENGTH(sResult) > LENGTH(sDelim) THEN
SET sResult = LEFT(sResult, LENGTH(sResult) - LENGTH(sDelim));
END IF;

RETURN sResult;
END;

////////////////////////////////////////
//выводим
////////////////////////////////////////
SELECT группа, funcReplace(группа) AS элементы FROM таблица_данных GROUP BY группа;
« Последнее редактирование: ґХЪРСам 12, 2008, 09:13:54 am от jx »

Оффлайн jx

  • Глобальный модератор
  • Коллежский асессор
  • *****
  • Сообщений: 291
  • Репутация: 16
  • Пол: Мужской
Re: MySQL замена в строке
« Ответ #5 : ґХЪРСам 17, 2008, 02:06:06 pm »
чё-то я намудрил с этими функциями  :fool:

вот нормальное решение:
Код: (sql) [Выделить]
/********************
* таблица с данными
********************/
DROP TABLE IF EXISTS данные;
CREATE TABLE данные
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
группа INT UNSIGNED NOT NULL,
значение INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);
/* заполняем */
INSERT INTO данные VALUES
/* {один, два, пять} */
(NULL, 1, 1),
(NULL, 1, 2),
(NULL, 1, 5),
/* {один, четыре} */
(NULL, 2, 4),
(NULL, 2, 1),
/* {три} */
(NULL, 3, 3),
/* {два, три} */
(NULL, 4, 3),
(NULL, 4, 8),
(NULL, 4, 2);


/********************
* таблица с названиями
********************/
DROP TABLE IF EXISTS названия;
CREATE TABLE названия
(
id INT UNSIGNED NOT NULL,
строка TINYTEXT NOT NULL,

PRIMARY KEY (id)
);
/* заполняем */
INSERT INTO названия VALUES
(1, 'один'),
(2, 'два'),
(3, 'три'),
(4, 'четыре'),
(5, 'пять');


/********************
* выводим
********************/
SELECT
данные.группа,
GROUP_CONCAT((SELECT названия.строка FROM названия WHERE данные.значение=названия.id) SEPARATOR ', ') AS элементы
FROM данные
GROUP BY группа

надо учить sql