"IN" hasonlító (MySQL)

laji · 2006. Jún. 23. (P), 21.56

SELECT * FROM t1 WHERE t1.a IN (felsorolás)

SELECT * FROM t1 WHERE t1.a IN (felsorolás)

Ha a "felsorolás"-t magam viszem be (php, dump, stb.), minden rendben. Ha a felsorolásként egy (ebből vagy másik táblából származó) szöveges (varchar) mezőt (oszlopot) adok meg, amiben szabályosan vesszővel vannak elválasztva a felsorolás elemei, a MySQL nem fogadja el, egyszerű szövegnek látja, nem felsorolásnak. Kijátszható ugyan az IN helyett a LIKE használatával, de (főként, hogy egész számokról van szó), ez jelentősen lelassítja a keresést. Van erre más megoldás?

■

Adatbázisok

csirip

csak némi tipp

Anonymous · 2006. Jún. 24. (Szo), 00.05

Nem értek hozzá, csak tipp:
Esetleg nincs véletlenül mysqlben valami string2felsorolás() vagy string2tömb([elválasztójel]) függvény? ... és ha a "felsorolás"-t előbb egy változóba teszed? @v:=felsorolas ?

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Sajnos, nem jött be

laji · 2006. Jún. 24. (Szo), 03.51

Függvény nincs (oda-vissza nyálaztam párszor), a változóba tétel meg ugyanúgy szövegként hagyja (ráadásul az előző sorból venné az értéket).

A hozzászóláshoz regisztráció és belépés szükséges
új téma

lehet, hogy tévedek,

TeeCee · 2006. Jún. 24. (Szo), 08.24

de sokszor fordult elő az ismerőseim között a következőkben vázolt probléma (~tervezési hiba), lehet, hogy Nálad is ilyen lenne?

Én a következőre tippelek (egy "képek behelyezése kategóriákba" problémán keresztül bemutatva):
"Photos" tábla: (photoid, name, categories)
"Categories" tábla: (catid, catname)
és ha jól sejtem, akkor a képek tábla categories mezőjében a catid-ek vannak felsorolva...

Ilyenkor használatos a kapcsolótábla ami után így nézne ki a dolog:
"Photos" tábla: (photoid, name [itt nincs categories mező!])
"Categories" tábla: (catid, catname)
"Cats_conn" tábla: (photoid, catid)

Ekkor ha egy kép több kategórába tartozik, a kategórianeveket megkapod az alábbi lekérdezés eredményeként
SELECT catname
FROM Photos AS p, Cats_conn AS conn, Categories AS c
WHERE p.photoid=conn.photoid AND conn.catid=c.catid
AND photoid=123

A hozzászóláshoz regisztráció és belépés szükséges
új téma

find_in_set ?

sotetbarna · 2006. Jún. 24. (Szo), 08.44

Hali!

Sajnos mysql hibánál (és általában minden hibakeresésénél) fontos lenne tudni, hogy milyen környezetben jön a hiba. Jó lett volna, ha egy create table, insert megelőzte volna a selectet, akkor talán könnyebb lenne segíteni.

Hoztam létre egy környezetet, hogy ki tudjam próbálni, mi lehet a probléma.

create table test1 (fint int, fchar varchar(30));
insert into test1 values (1,'11,33,44,55,66,77');
insert into test1 values (2,'11,33,44,55,66,77');
insert into test1 values (11,'11,33,44,55,66,77');
insert into test1 values (55,'11,33,44,55,66,77');
insert into test1 values (77,'11,33,44,55,66,77');
select *, fint in (fchar) as 'in', find_in_set(fint,fchar) as 'find_in_set' from test1;
+------+-------------------+------+-------------+
| fint | fchar | in | find_in_set |
+------+-------------------+------+-------------+
| 1 | 11,33,44,55,66,77 | 0 | 0 |
| 2 | 11,33,44,55,66,77 | 0 | 0 |
| 11 | 11,33,44,55,66,77 | 1 | 1 |
| 55 | 11,33,44,55,66,77 | 0 | 4 |
| 77 | 11,33,44,55,66,77 | 0 | 6 |
+------+-------------------+------+-------------+


create table test1 (fint int, fchar varchar(30));

insert into test1 values (1,'11,33,44,55,66,77');
insert into test1 values (2,'11,33,44,55,66,77');
insert into test1 values (11,'11,33,44,55,66,77');
insert into test1 values (55,'11,33,44,55,66,77');
insert into test1 values (77,'11,33,44,55,66,77');

select *, fint in (fchar) as 'in', find_in_set(fint,fchar) as 'find_in_set' from test1;
+------+-------------------+------+-------------+
| fint | fchar             | in   | find_in_set |
+------+-------------------+------+-------------+
|    1 | 11,33,44,55,66,77 |    0 |           0 |
|    2 | 11,33,44,55,66,77 |    0 |           0 |
|   11 | 11,33,44,55,66,77 |    1 |           1 |
|   55 | 11,33,44,55,66,77 |    0 |           4 |
|   77 | 11,33,44,55,66,77 |    0 |           6 |
+------+-------------------+------+-------------+

A find_in_set esetleg segíthet.

Barna

A hozzászóláshoz regisztráció és belépés szükséges
új téma

A teljes felállás

laji · 2006. Jún. 24. (Szo), 10.43

Elnézést, hogy nem adtam meg pontosabban a felállást. Mielőtt kipróbálom a javaslatokat, itt van (csak a lényeg):

# MySQL betöltés (dump)
SET NAMES utf8;
CREATE TABLE `aru` (
`SORSZAM` int(5) unsigned NOT NULL default '0',
`LEIRAS` text CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (`SORSZAM`),
UNIQUE KEY `SORSZAM` (`SORSZAM`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
CREATE TABLE `vevo` (
`SORSZAM` int(5) unsigned NOT NULL auto_increment,
`AZON` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL default '',
`NEV` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
`KOSAR` text CHARACTER SET utf8 COLLATE utf8_general_ci,
PRIMARY KEY (`SORSZAM`),
UNIQUE KEY `SORSZAM` (`SORSZAM`),
UNIQUE KEY `AZON` (`AZON`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

# MySQL betöltés (dump)
SET NAMES utf8;
CREATE TABLE `aru` (
 `SORSZAM` int(5) unsigned NOT NULL default '0',
 `LEIRAS` text CHARACTER SET utf8 COLLATE utf8_general_ci,
 PRIMARY KEY (`SORSZAM`),
 UNIQUE KEY `SORSZAM` (`SORSZAM`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE `vevo` (
 `SORSZAM` int(5) unsigned NOT NULL auto_increment,
 `AZON` varchar(45) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL default '',
 `NEV` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci,
 `KOSAR` text CHARACTER SET utf8 COLLATE utf8_general_ci,
 PRIMARY KEY (`SORSZAM`),
 UNIQUE KEY `SORSZAM` (`SORSZAM`),
 UNIQUE KEY `AZON` (`AZON`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

<?php
// Kosarba tesz
$azon=$sor_eredmenyBelepett['AZON'];
$eredmenyKosar=mysql_query(sprintf("
SELECT KOSAR FROM vevo WHERE AZON='%s'
", $azon));
$sor_eredmenyKosar=mysql_fetch_assoc($eredmenyKosar);
// Mar van vmi a kosarban (pl. "17,91,821").
$kosarTomb=explode(",", $sor_eredmenyKosar['KOSAR']);
$kosarTomb[]=411; // Peldaul.
sort($kosarTomb);
$kosarFelsorolas=implode(",", $kosarTomb);
$modositasKosarba=mysql_query(sprintf("
UPDATE vevo SET KOSAR='%s'
", $kosarFelsorolas));
?>

<?php
// Kosarba tesz
$azon=$sor_eredmenyBelepett['AZON'];
$eredmenyKosar=mysql_query(sprintf("
   SELECT KOSAR FROM vevo WHERE AZON='%s'
   ", $azon));
$sor_eredmenyKosar=mysql_fetch_assoc($eredmenyKosar);
// Mar van vmi a kosarban (pl. "17,91,821").
$kosarTomb=explode(",", $sor_eredmenyKosar['KOSAR']);
$kosarTomb[]=411; // Peldaul.
sort($kosarTomb);
$kosarFelsorolas=implode(",", $kosarTomb);
$modositasKosarba=mysql_query(sprintf("
   UPDATE vevo SET KOSAR='%s'
   ", $kosarFelsorolas));
?>

MySQL lekérdezés, ami nem működik. Csak szövegnek látja a felsorolást, tehát úgy alakítja számmá, hogy törli az első vessző utáni részt. Példámban így abból, hogy "17,91,411,821", marad 17 mint egész szám, s így természetesen a COUNT(*) az 1. Ha kézzel viszem be (vagy php-vel), hogy "17,91,411,821", kifogástalan: COUNT(*)=4.

SET NAMES utf8;
SELECT vevo.*,
(
SELECT COUNT(*)
FROM aru
WHERE aru.SORSZAM IN (vevo.KOSAR)
) AS KOSAR_DB
FROM vevo
ORDER BY vevo;

SET NAMES utf8;
SELECT	vevo.*,
			(
			SELECT	COUNT(*)
			FROM		aru
			WHERE		aru.SORSZAM IN (vevo.KOSAR)
			)			AS KOSAR_DB
FROM		vevo
ORDER BY	vevo;

Ami működik (de lassú):

SELECT vevo.*,
(
SELECT COUNT(*)
FROM aru
WHERE CONCAT(',', vevo.KOSAR, ',') LIKE CONCAT('%,', aru.SORSZAM, ',%')
) AS KOSAR_DB
FROM vevo
ORDER BY vevo;

SELECT	vevo.*,
			(
			SELECT	COUNT(*)
			FROM		aru
			WHERE		CONCAT(',', vevo.KOSAR, ',') LIKE CONCAT('%,', aru.SORSZAM, ',%')
			)			AS KOSAR_DB
FROM		vevo
ORDER BY	vevo;

Felmerülhet, hogy miért bonyolítom így, s nem számoltatom meg egyszerűen a php-vel a kosár darabszámát.
Válasz: mert egy összetettebb lekérdezésről van szó, több allekérdezéssel, s ha egybe fogom az egészet, akkor tudok rendezni a MySQL-ban igény szerint, s már eleve így kapom meg az adatokat. Sajnos, a php rendezőképessége igen gyenge.
Tkp. elég lett volna egy olyan MySQL függvény is, ami egy vesszőkkel tagolt szövegből képes visszaadni a darabszámot - de ilyen nincs.

UI
A FIND_IN_SET-et megnéztem. Valószínűleg működne is, hiszen eleve szöveghez készült, de sajnos, nem darabszámot, hanem találati helyet ad vissza.

UI 2.
Ha más tanács nem lesz, akkor úgy tűnik, TeeCee ajánlata lesz a megoldás. Kár, hogy ez eggyel több táblát kíván.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

mit is szeretnél?

sotetbarna · 2006. Jún. 24. (Szo), 12.35

az in logikai értéket ad vissza, a find_in_set pedig egy számot, ami ha talált, akkor ugye nagyobb, mint nulla, azaz ha find_in_set(x,y)>0, akkor igaz.

szerintem belekeveredtél dolgokba.

amikor azt írtad, hogy nem php-vel akarod szétdobni, mert a mysql rendez csak jól, akkor nem gondoltál bele, hogy csak a vevőre rendezel (az idézett mysql kód szerint), amiatt a kódrészlet miatt nyugodtan lehet php-vel számoltatni (arról nem is beszélve, hogy a php igenis tud jól rendezni utf8-at)

aztánmeg sztem azt sem gondoltad végig, ha lesz mondjuk 1000 árud és 2000 vevőd, akkor a fenti sql vajon milyen sebességgel fut majd le?

próbálj ki más megközelítést és táblaszerkezetet (például TeeCee-ét)

Barna

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Agyamra ment a hőség :))

laji · 2006. Jún. 24. (Szo), 16.10

...tényleg ideillik a FIND_IN_SET:
WHERE find_in_set(aru.SORSZAM,vevo.KOSAR)>0

Másrészt végiggondoltam, sebességügyileg kellenek azok a kapcsoló táblák - jól gondolta TeeCee (azaz mindhárman), hogy miben hibáztam.

(A rendezésről: ez csak egy leegyszerűsített részlet, valójában vagy 10-15 oszlop alapján lehet rendezni és szűrni. S ha mindezt egy lekérdezésben oldom meg, akkor POST értékekkel megy MySQL-on belül.)

Sokat segített mindkét dolog, köszönöm mindenkinek! További kellemes hétvégét.

A hozzászóláshoz regisztráció és belépés szükséges
új téma

WHERE IN nem működik paraméterrel

Anonymous · 2006. Jún. 24. (Szo), 13.01

WHERE-ben levő IN-t nem lehet paraméterezni, azaz statikusan kell felsorolni az értékeket. Ez nem a MySQL hibája.

Egyébként pont azt a hibát követed el, amit TeeCee ír a 3. hsz-ban.
Relációs adatbázisban a relációkat ne felsorolásként tárold, vagy ha mégis, akkor viseld a következményeit (extra feldolgozás, lassúság, nehéz kezelhetőség).

A rendes megoldás az lenne, hogy létrehozol egy kosár táblát (csak példa):
user_id
product_id
quantity
add_date

Ha kiváncsi vagy egy felhasználó termékeire:
select count(*) from cart where user_id = current_user_id
vagy
select sum(quantity) from cart where user_id = current_user_id

Azt gondolom most is figyeled, hogy ha ugyanazt a terméket adják hozzá mégegyszer, akkor vagy nem adja hozzá, vagy a mennyiséget hozzáadja a már bentlévő mennyiséghez.

A dátum azért kell, hogy néha lehessen takarítani, pl. az egy hónapja bent lévő termékeket. Előtte küldhetsz egy figyelmeztetést, így plusz esélyed van, hogy meg is rendelik a termékeket.

Egyáltalán nem kár, hogy plusz egy táblát igényel, az a kár, hogy bárkinek eszébe jut ezen spórolni (spórolni? inkább plusz munkát csinál).

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Igen, belátom...

laji · 2006. Jún. 24. (Szo), 16.14

...mint eggyel fentebb írtam, s így fogom átalakítani a táblákat. Köszönöm!

A hozzászóláshoz regisztráció és belépés szükséges
új téma

nem a táblák száma számít

Hodicska Gergely · 2006. Jún. 27. (K), 02.24

Kár, hogy ez eggyel több táblát kíván.

Ezen kár spórolnod, inkább azt nézd, hogy mit nyersz általa. Egyrészt amit a többiek is mondtak, plusz lehetőséged lesz például külső kulcsok használatára, ami nagyon jól jöhet adatbázis integritásának megőrzéséhez, plusz általa egy csomó funkciót nem kell PHP-ban megoldanod.

Felhő

A hozzászóláshoz regisztráció és belépés szükséges
új téma

Weblabor

Keresés