Vinnaren i pepparkakshustävlingen!
  1. Dator och IT
  2. Systemutveckling
  3. Databaser

MySQL Stored procedure, spara select i variabel + annat

Svara
2010-12-15, 21:23
  #1
trellen
Medlem
Hej allesammans,

Jag försöker utveckla en applikation som kommer kommunicera med en databas rejält mycket. Jag kom på därefter att det nog vore en bra ide både säkerhetsmässigt och för prestanda skäl om man kunde överlämna så mycket arbete till MySQL som möjligt och det försöker jag göra nu, men jag har några frågor.

Dem senaste timmarna så har jag lärt mig lite SQL syntax för att skriva ok enkla "stored procdure". Detta är den svåraste jag har gjort hitintills:
Kod: 
DELIMITER //

CREATE PROCEDURE add_user (IN email VARCHAR(255),
                            IN password VARCHAR(64),
                            OUT user_id INT UNSIGNED,
                            OUT private_id BINARY(16)
)
BEGIN
    DECLARE my_uuid char(37);
    
    IF NOT EXISTS (SELECT ID FROM User WHERE Email=email) THEN
        
        set private_id = BinaryUUID();
        
        INSERT INTO User (Email, Password, PrivateID) VALUES (email,unhex(sha1(concat("salt",password))),private_id);
        
        SELECT ID INTO user_id FROM User WHERE Email=email;
        
    ELSE
    
        SELECT PrivateID INTO private_id FROM User WHERE Email=email;
        
        set user_id = LAST_INSERT_ID();
        
    END IF;
END//

DELIMITER ;

Problemet är att som ni ser så utför flertals förfrågningar, hur kan man strukturera om den så den blir lite mer intelligent och sparar resultatet och återanvänder det? Läst lite om CURSOR(S), men har aldrig kunnat få det fungera, antingen så blir det fel eller så returneras NULL

Några andra frågor jag har är, är en hel stored prodcedure atomic och säker köra flertals körningar samtidigt? D.v.s om en stored prodcedure ändrar i någon tabell, garanterar MySQL att dem aldrig kommer köras exakt samtidigt (ändringarna) så t.ex LAST_INSERT_ID kommer alltid returnera det senaste inlagda id av den stored prodcedure som kör den, även om kanske det har hunnit läggas till en ny post? Om inte, hur fixar jag det?

OBS. Jag har aldrig programmerat i SQL sedan tidigare direkt.

Tack på förhand och så önskar jag er en God Jul!
Citera
2010-12-15, 22:38
  #2
kh31d4r
Medlem
kh31d4rs avatar
Citat:
Några andra frågor jag har är, är en hel stored prodcedure atomic och säker köra flertals körningar samtidigt? D.v.s om en stored prodcedure ändrar i någon tabell, garanterar MySQL att dem aldrig kommer köras exakt samtidigt (ändringarna) så t.ex LAST_INSERT_ID kommer alltid returnera det senaste inlagda id av den stored prodcedure som kör den, även om kanske det har hunnit läggas till en ny post? Om inte, hur fixar jag det?

Nej, du måste använda transaktioner för detta, vilka ej stöds av MyISAM, utan du får köra till exempel InnoDB som dock är ACID-compliant osv.
Citera
2010-12-16, 12:01
  #3
PerWi
Medlem
Du skulle kanske kunna skapa en temp tabell, vet inte exakt hur man gör i MySQL men i MSSQL:

CREATE TABLE #tempUser (
ett, två, osv... det du vill lagra
);
INSERT #tempUser
SELECT
det du vill hämta
FROM user

sen kan du köra
SELECT id eller * eller dylikt
FROM #tempUser

drop table #tempUser


Nu sätter du även in värden men kan vara bra att känna till om man gör många förfrågningar mot en tabell.
Citera
2010-12-20, 23:40
  #4
trellen
Medlem
Ursäktar att jag inte har svarat på ett tag, jag har varit upptagen.

Citat:
Ursprungligen postat av kh31d4r
Nej, du måste använda transaktioner för detta, vilka ej stöds av MyISAM, utan du får köra till exempel InnoDB som dock är ACID-compliant osv.
Jag förstår. Är det något jag bör känna till som du kan tipsa mig på direkten? Har redan börjat läsa lite om det, och då verkade det som att innodb endast ger en kopia av den datan man arbetar med i transaktionen, d.v.s den kommer fungera, men inte vara helt uppdaterad pg.a av gammal information (som först applikationen blir medveten om efter transaktionen). Antar dock om att jag får läsa lite i manualerna på mysql.com istället för mer korrekt information.


Citat:
Ursprungligen postat av PerWi
Du skulle kanske kunna skapa en temp tabell, vet inte exakt hur man gör i MySQL men i MSSQL:

CREATE TABLE #tempUser (
ett, två, osv... det du vill lagra
);
INSERT #tempUser
SELECT
det du vill hämta
FROM user

sen kan du köra
SELECT id eller * eller dylikt
FROM #tempUser

drop table #tempUser


Nu sätter du även in värden men kan vara bra att känna till om man gör många förfrågningar mot en tabell.

Detta verkar utan tvekan intressant. Om man kunde skapa tabeller "on the fly" så skulle man ju teoretiskt kunna undkomma dem flesta synkroniseringsproblem som antagligen skulle möjliggöra bättre prestanda. Sannolikheten är dock att det är kanske helt i onödan, och kanske det är en kostsam process att skapa tabeller och flytta över data till dem. Vet någon något om detta?

Har någon erfarenhet om hur man skapar tabeller "on the fly", säg att jag vill utföra typ:
Kod: 
CREATE TABLE IF NOT EXISTS (SELECT ...)
{
}
Så ska en tabell med namnet som förfrågningen returnerar skapas (om den inte existerar) och påverkade rader ska vara 1?


Tack på förhand!
Citera
2010-12-21, 08:24
  #5
PerWi
Medlem
Citat:
Ursprungligen postat av trellen
Detta verkar utan tvekan intressant. Om man kunde skapa tabeller "on the fly" så skulle man ju teoretiskt kunna undkomma dem flesta synkroniseringsproblem som antagligen skulle möjliggöra bättre prestanda. Sannolikheten är dock att det är kanske helt i onödan, och kanske det är en kostsam process att skapa tabeller och flytta över data till dem. Vet någon något om detta?

Har någon erfarenhet om hur man skapar tabeller "on the fly", säg att jag vill utföra typ:
Kod: 
CREATE TABLE IF NOT EXISTS (SELECT ...)
{
}
Så ska en tabell med namnet som förfrågningen returnerar skapas (om den inte existerar) och påverkade rader ska vara 1?

När olika slags frågor ställs mot samma tabell på flera ställen i tex. en procedure är det bättre att ställa den mot temptabellen. Det är iaf min erfarenhet, har arbetat en del med det på slutet och det går definitivt snabbare. För i den har du endast de data du vill ha, du behöver inte söka i allt. Du kan ju använda en WHERE-sats för att begränsa vilken data du vill lagra i den.

Tror inte du behöver creata table if not exists då du skapar och droppar den varje gång proceduren körs.
Citera
2010-12-21, 12:34
  #6
Proton
Moderator
Protons avatar
Citat:
Ursprungligen postat av PerWi
När olika slags frågor ställs mot samma tabell på flera ställen i tex. en procedure är det bättre att ställa den mot temptabellen. Det är iaf min erfarenhet, har arbetat en del med det på slutet och det går definitivt snabbare. För i den har du endast de data du vill ha, du behöver inte söka i allt. Du kan ju använda en WHERE-sats för att begränsa vilken data du vill lagra i den.

Tror inte du behöver creata table if not exists då du skapar och droppar den varje gång proceduren körs.
Syntaxen för detta i mysql är
Kod:
CREATE TEMPORARY TABLE blabla.... 
och en temporär tabell finns i databasen ända tills man stänger uppkopplingen med vilken tabellen skapades. Av den anledningen kan det ju vara ide att ha med "IF NOT EXISTS" så undviker man att proceduren kraschar av den anledningen iaf. En annan ide kan ju även vara att kasta ut den temporära tabellen om den redan finns(eller köra truncate på den) innan man börjar manipulera data i den för att vara säker på att det inte ligger kvar nåt gammalt jox i den.
Citera
2010-12-21, 13:23
  #7
PerWi
Medlem
Citat:
Ursprungligen postat av Proton
och en temporär tabell finns i databasen ända tills man stänger uppkopplingen med vilken tabellen skapades. Av den anledningen kan det ju vara ide att ha med "IF NOT EXISTS" så undviker man att proceduren kraschar av den anledningen iaf. En annan ide kan ju även vara att kasta ut den temporära tabellen om den redan finns(eller köra truncate på den) innan man börjar manipulera data i den för att vara säker på att det inte ligger kvar nåt gammalt jox i den.

Okej det visste jag inte, ska kolla upp det i min SP.
Citera
Svara

Stöd Flashback

Flashback finansieras genom donationer från våra medlemmar och besökare. Det är med hjälp av dig vi kan fortsätta erbjuda en fri samhällsdebatt. Tack för ditt stöd!

Stöd Flashback