Kommentarer i MySQL-forespørgsler

En lille idé man kan vælge at bruge eller ej :)

Har man et site der køre nogle store og tunge forespørgsler, og hvis man er bange for at nogle af dem løber løbsk eller ophober sig, kan man fx i starten af sin SQL indsætte en lille kommentar.

Tricket er nemlig at den kommentar kommer med når man laver en:

SHOW PROCESSLIST

Efterfølgende sætter man et cron-job op der hvert minut kigger efter forespørgsler, og hvis scriptet opdager en forespørgsel der indeholder en bestemt kommentar kan man enten automatisk dræbe den, eller sende en mail om at en forespørgsel er løbet løbsk.

Kommentaren kunne fx indeholder en maks. kørselstid.

Et eksempel på en forespørgsel hvor man kun vil lave den køre i 120 sek.:

1
2
3

<?php
  mysql_query("/* MAX_120 */ SELECT big_stuff FROM big_table");
?>

PHP-scriptet kunne se sådan ud:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

<?php
  mysql_connect("localhost", "benny", "pa55word");
  mysql_select_db("benny_db");
 
  $result = mysql_query("SHOW PROCESSLIST");
 
  while($row = mysql_fetch_assoc($result)) {
 
    // Matcher /* MAX_120 */
    if (preg_match("/\/\*\s*max_([0-9]+)\s*\*\//i",
             $row['Info'], $regs)) {
 
      // Kontrollerer om forespørgslen har kørt længe end den må
      if ($row['Time'] > $regs[1]) {
        mysql_query("KILL ".(int) $row['Id']);
      }
 
    }
  }
?>

mysqltop.sh

Jeg manglede en “mysqltop” der kunne vise hvilke forespørgsler der kørte på min database. Løsningen blev:

#!/bin/sh
 
watch -n 1 'mysql -uprocess -pxxx dbname<<EOF
show processlist;
EOF'

Lidt stored procedure

Som inspiration til stored procedure i MySQL har jeg lavet en lille eksempel.

Stored procedure er en nem måde at “gemme” noget SQL på serveren. Lave en funktion om man vil. Mit eksempel er igen lidt tænkt, men burde illustrere hvad man kan bruge stored procedures til.

Først opretter jeg to meget simple tabeller. En til brugere og en til rettigheder.

1
2
3
4
5
6
7
8
9
10
11

CREATE TABLE priv (
  userid INT NOT NULL auto_increment,
  area CHAR(10) NOT NULL,
  PRIMARY KEY (userid,area)
) ENGINE=MyISAM;
 
CREATE TABLE users (
  userid INT NOT NULL auto_increment,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (userid)
) ENGINE=MyISAM;

Når jeg opretter en bruger vil jeg gerne automatisk gi’ den bruger adgang til områderne foo og bar. Jeg skal altså indsætte en række i users-tabellen og to rækker i priv-tabellen. Rækkerne i priv skal matche det userid man indsatte i users.

Jeg laver følgende stored procedure:

1
2
3
4
5
6
7
8

CREATE PROCEDURE add_user (IN i VARCHAR(255))
BEGIN
  DECLARE u INT;
  INSERT INTO users (name) VALUES (i);
  SELECT LAST_INSERT_ID() INTO u;
  INSERT INTO priv(userid, area) VALUES(u, 'foo');
  INSERT INTO priv(userid, area) VALUES(u, 'bar');
END;

Hvor jeg som argument ta’r en VARCHAR(255). I min stored procedure indsætter jeg brugeren i users-tabellen. Henter userid via LAST_INSERT_ID() og indsætter to rækker i priv-tabellen med områderne foo, bar sammen med userid.

Tilsvarende laver jeg en remove_user() der kan slette brugeren og hans privilegier

1
2
3
4
5
6
7

CREATE PROCEDURE remove_user(IN i VARCHAR(255))
BEGIN  
  DECLARE u INT;
  SELECT userid INTO u FROM users WHERE name = i;
  DELETE FROM priv WHERE userid = u;
  DELETE FROM users WHERE userid = u;
END;

For at indsætte nogle brugere kalder jeg følgende:

1
2
3

CALL add_user('Anna');
CALL add_user('Bent');
CALL add_user('Casper');

Og for at slette kalder jeg:

1

Call remove_user('Bent');

Bemærk at hvis man opretter stored procedures via phpMyAdmin så skal man skifte delimiter fra ; til fx //.

En måde at bruge VIEWs på

Jeg har lavet en simpel klasse der repræsenterer en MySQL-tabel. Den er meget simpel, og i constructor’en angiver man tabelnavn og primærnøgle, og så laver den bare en

1

SELECT * FROM tabelnavn WHERE id = $id;

Hvor tabelnavn og id er variable. Når man laver en instans af klassen udfra en user-tabel med id 5 laver man en

1

$user = new User(5);

Det oversætter min klasse så til:

1

SELECT * FROM user WHERE userid = 5;

Hvis jeg vel at mærke har kaldt min tabel user og primærnøglen userid i min klasse. Det der efterfølgende sker er, at klassen hælder alle felterne over i et array, og jeg kan efterfølgende hive feltet "name" ud som $user->getName(), eller gemme via $user->setName('Morten') der udfører en

1

UPDATE tabel SET name = 'Morten' WHERE userid = 5;

For at spare nogle databaseopslag kan man som andet argument selv sende felterne med. Fx.

1
2

$row = mysql_fetch_assoc($result);
$user = new User($row['userid'], $row);

På den måde behøver min klasse ikke lave noget opslag. Det var egentlig ikke klassen jeg ville snakke om. Skriv hvis I vil se noget kode eller høre mere :)

Det jeg ville nævne var en smart finte (synes jeg selv :)). Først laver jeg en tabel til mine brugere:

1
2
3
4
5
6

CREATE TABLE user (
  userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  newsletter TINYINT(1) UNSIGNED NOT NULL,
  PRIMARY KEY (userid)
);

Så fylder jeg lidt folk i:

1
2
3
4
5
6

INSERT INTO user (name, gender, newsletter) VALUES
('Alfred', 1),
('Bent', 0),
('Conni', 1),
('Dorthe', 1),
('Else', 0);

Nu laver jeg et VIEW til folk der abonnerer på nyhedsbrevet:

1
2

CREATE VIEW newsletter AS
  SELECT *  FROM user WHERE newsletter = 1;

Det resultat som min VIEW returnerer kan jeg umiddelbart smide direkte over i min MySQL-klasse som:

1
2
3
4

$result = mysql_query("SELECT * FROM newsletter");
while($row = mysql_fetch_assoc($result)) {
  $user = new User($row['userid'], $row);
}

Min klasse laver aldrig noget opslag selv, og da den ved hvilken tabel det stammer fra kan jeg også fint gemme. Det er måske nemmere at se det anvendelige hvis mit VIEW var mere kompliceret. Men forestil jer at jeg fx vil sende en e-mail til alle brugere at BedsteVen.dk der har oprettet en annonce, men ikke tilføjet noget billede, og deres annonce udløber om 14 dage, og der er mindre end 10 personer der har set den. Så begynder det at være meget rart at have det som et VIEW.

Om at sende nyhedsbreve

Jeg synes det er problematisk at sende mange nyhedsbreve ud. Især hvis alle nyhedsbrevene skal være unikke.

Jeg står tit i en situation hvor jeg har et site som fx BedsteVen.dk der er kodet i PHP op mod en MySQL database. For at kunne bruge al den logik der i forvejen findes på sitet, vælger jeg typisk at udsende de enkelte e-mails gennem PHP ved at kalde et script via wget.

Det er dejlig nemt at kode, men det er utrolig svært hvis man via et script skal sende mange mails (mange er mere end 300 mails).

PHP har en max_execution_time der afgør levetiden for scriptet. Typisk er den på 90 sekunder, og hvis man er længere tid om at sende end det, dør scriptet.

Jeg lavede nogle test på BedsteVen.dk, og via PHP’s mail() funktion kunne jeg over en periode på 90 sekunder udsende ca. 300 mails. Det svarer til 3,33 mails i sekundet.

Min løsning blev to-trins.

Først valgte jeg at begrænse mængden af mails til hvad serveren sikkert kunne klare:

1
2
3
4

$count = 3 * ini_get(max_execution_time);
foreach(getUsers($count) as $user) {
  $user->sendNewsletter();
}

Ovenstående skulle gerne illustrere at jeg henter 3×90=270 brugere ud, og sender dem et nyhedsbrev. Det burde gøre at scriptet ikke dør undervejs.

Næste trin var at jeg i bunden af scriptet indsatte et link til scriptet igen, og kalder wget rekursivt så den blev ved med at hente siden — indtil der ikke længere er noget link. Ovenstående eksempel skal derfor udvides til at checke for at jeg ikke sender samme mail to gange.

For at få wget til at opføre sig som jeg vil, kalder jeg den på følgende måde:

1

  wget -q -erobots=off -w 5 -l 100 -r --delete-after -nd [URL]

-q gør at der ikke kommer noget output
-erobots=off gør at den ikke henter robots.txt
-w 5 gør at wget venter 5 sek. mellem hver download
-l 100 sætter en grænse for hvor mange niveauer wget må følge
-r er rekursiv
--delete-after og -nd gør at wget rydder op efter sig selv

Om det er den smarteste måde at gøre det på ved jeg ikke rigtig. Det er én måde :)

Lidt mere INSERT

INSERT har en lille ekstra feature som de færeste kender til. De fleste kender INSERT IGNORE eller REPLACE INTO som man kan bruge hvis man indsætter noget der overlapper en unik eller primær nøgle. Men der findes også en ON DUPLICATE KEY det gør det muligt at lave lidt mere avancerere ting. Jeg demonstrerer med et lille eksempel.

Jeg har en tabel til tags:

1
2
3
4
5

CREATE TABLE tags (
  tag VARCHAR(255),
  cnt INT UNSIGNED DEFAULT '1',
  PRIMARY KEY (tag)
) ENGINE = MYISAM;

Bemærk at cnt som standard er 1 og at tag er min primære nøgle. Jeg vil i tag gemme tag-navnet og i cnt gemme antallet af tags. Jeg indsætter data på følgende måde:

1
2
3
4
5
6
7
8

INSERT INTO tags (tag) VALUES ('foo')
   ON DUPLICATE KEY UPDATE cnt=cnt+1;
 
INSERT INTO tags (tag) VALUES ('foo')
   ON DUPLICATE KEY UPDATE cnt=cnt+1;
 
INSERT INTO tags (tag) VALUES ('foo')
   ON DUPLICATE KEY UPDATE cnt=cnt+1;

Feltet cnt indeholder nu 3, og der findes kun én række med “foo”.

Første gang findes foo ikke i forvejen, og cnt bliver derfor 1. Anden gang er foo der og cnt bliver talt op. Samme gør sig gældende de efterfølgende gange.

Det er måske et lidt tænkt eksempel, men det illustrerer rimelig godt hvordan ON DUPLICATE KEY virker.

Score på MySQL-søgninger

Skal man søge i nogle store tekster i en MySQL-tabel kan man med fordel benytte sig af MySQL’s Full-Text søgninger. En funktion der er speciel anvendelig er dens mulighed for at rangordne efter hvor godt ens søgeord matcher det fundne. Jeg har lavet et lille eksempel:

Først lavede jeg en simpel tabel med et TEXT-felt med et FULLTEXT-index på:

1
2
3
4
5
6

CREATE TABLE lipsum (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  str TEXT NOT NULL,
  PRIMARY KEY (id),
  FULLTEXT KEY str (str)
) ENGINE=MyISAM;

Så hentede jeg en masse tekst fra lipsum.com og lagde det ind.

En søgning i tabellen kan se ud som følgende:

1
2
3

SELECT * FROM lipsum
WHERE MATCH (str)
  AGAINST ('tempus pede +lorem -ipsum' IN BOOLEAN MODE);

Hvor jeg søger efter tekster der indeholder ‘tempus’ eller ‘pede’ og ‘lorem’ men uden ‘ipsum’. Da søgningen er lidt kompliceret vil jeg måske gerne ha’ en vægtning på, så de mest relevante resultater kommer først. Dette løser jeg på følgende måde:

1
2
3
4
5

SELECT *, MATCH (str)
  AGAINST ('tempus pede +lorem -ipsum' IN BOOLEAN MODE) AS score
FROM lipsum
HAVING score > 0
ORDER BY score DESC;

Hvor jeg først laver søgningen og kalder den ‘score’ og senere indskrænker min søgning med HAVING til kun at indeholde dem hvor score er højere end 0. Bemærk at man skal bruge HAVING og ikke WHERE.

Tælle rækker, også med LIMIT

I mange tilfælde har man en webside der trækker en liste over ting fra en database. Det kan være en liste over brugere, annoncer, artikler m.m. Sådanne liste har det med at blive lange, og man vil inddele den i flere sider af fx 25 rækker.

I MySQL er det rimelig nemt at begrænse sit data. Det gøres bare ved at bruge LIMIT. Fx:

1

SELECT * FROM foo LIMIT 25;

Eller med et offset:

1

SELECT * FROM foo LIMIT 50, 25;

Hvor MySQL returnerer 25 rækker startende fra række 50.

Men så kommer problemet. Man skal jo også vise hvor mange sider ens data er inddelt i. Bruger man PHP’s mysql_num_rows() vil den kun returnerer 25 selvom man måske har flere tusinde rækker.

Jeg har desværre set en del der bare dropper LIMIT, og så beskærer dataene via PHP. Dette er selvfølgelig tåbeligt, da man arbejder med alle rækkerne hver gang.

Nogle suplerer deres SELECT med en tilsvarende query bare med SELECT COUNT(*) i stedet. Dette er bedre, men hvis ens query er meget kompliceret kan det give noget grim kode.

Den smarte løsning er at tilføje SQL_CALC_FOUND_ROWS til sin SELECT. Bruger man den kan man umiddelbart efter ens query har kørt hente antallet af rækker MySQL ville ha’ returneret, hvis den ikke var begrænset af en LIMIT. Dette gøres ved at kalde FOUND_ROWS().

Jeg har lavet et lille eksempel. Først opretter jeg en test-tabel:

1
2
3
4

CREATE TABLE letters (
    letter char(1) NOT NULL,
    PRIMARY KEY (letter)
);

Så indsætter jeg noget data (26 bogstaver):

1
2
3
4

INSERT INTO letters (letter) VALUES ('a'), ('b'), ('c'),
('d'), ('e'), ('f'), ('g'), ('h'), ('i'), ('j'), ('k'),
('l'), ('m'), ('n'), ('o'), ('p'), ('q'), ('r'), ('s'),
('t'), ('u'), ('v'), ('w'), ('x'), ('y'), ('z');

Nu vil jeg hente tre rækker, men for eksemplets skyld vil jeg springe de to første over:

1
2

SELECT SQL_CALC_FOUND_ROWS * FROM letters
ORDER BY letter ASC LIMIT 2, 3;

Hvilket returnerer c, d og e. Umiddelbart bagefter min SELECT skal jeg kalde FOUND_ROWS() for at få det totale antal rækker:

1

SELECT FOUND_ROWS() AS total_cnt;

Den returnerer en total_cnt på 26. Hvilket passer fint med det antal jeg indsatte.

Det er vigtigt at man kalder FOUND_ROWS() lige efter ens SELECT.

Ugenumre i MySQL

Et lille fif som de færeste sikkert ikke tænker over: Når man arbejder med ugenumre er der mange forskellige definitioner. Den danske definition siger at uge 1 er den uge der har mere end tre dage i det nye år. Det betyder at hvis man laver en

1

SELECT WEEK('2008-09-22 07:08:04');

Så får man 38, men d. 22. sep. 2008 ligger i uge 39. Derfor skal man kalde WEEK() med et parameter der angiver hvilken ugestandart man vil anvende. For Danmark er det 3. Man skal derfor lave sin forespørgsel som:

1

SELECT WEEK('2008-09-22 07:08:04', 3);

Hvilket også returnerer 39.

EXISTS i MySQL

Jeg har tit en en-til-mange datastruktur når jeg fx arbejder med ting der har vedhæftet billeder. Det kan fx være på BedsteVen.dk, hvor brugerne kan have flere billeder på deres dyr eller annoncer.

På forsiden af BedsteVen.dk viser jeg nogle tilfældige dyr, men jeg vil kun vise de dyr der indeholder billeder. Min struktur er noget lignende (meget simplificeret):

Først tabellen med dyrene:

1
2
3
4
5

CREATE TABLE item (
    itemid INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (itemid)
) ENGINE = MYISAM;

Så tabellen med billederne:

1
2
3
4
5

CREATE TABLE image (
    imageid INT UNSIGNED NOT NULL AUTO_INCREMENT,
    path VARCHAR(255) NOT NULL,
    PRIMARY KEY(imageid)
) ENGINE = MYISAM;

Til sidst tabellen der binder dyr og billeder sammen:

1
2
3
4
5

CREATE TABLE items_and_images (
    itemid INT UNSIGNED NOT NULL,
    imageid INT UNSIGNED NOT NULL,
    PRIMARY KEY(itemid, imageid)
) ENGINE=MyISAM;

Så hælder vi data i tabellerne:

1
2

INSERT INTO item (itemid, name) VALUES (1, 'Alfa'), (2, 'Beta'),
(3, 'Charlie'), (4, 'Delta'), (5, 'Echo'), (6, 'Foxtrot');

1
2
3
4
5
6

INSERT INTO image (imageid, path) VALUES
(1, 'images/001.png'), (2, 'images/002.png'),
(3, 'images/003.png'), (4, 'images/004.png'),
(5, 'images/005.png'), (6, 'images/006.png'),
(7, 'images/007.png'), (8, 'images/008.png'),
(9, 'images/009.png'), (10, 'images/010.png');

1
2
3

INSERT INTO items_and_images (itemid, imageid)
VALUES (1, 1), (1, 2), (1, 3), (1, 4), (2, 5), (2, 6),
(3, 7), (3, 8), (3, 9), (3, 10);

For at hente navnet ud fra item-tabellen på alle de rækker hvor den pågældende række var i items_and_images brugte jeg EXISTS, og SQL’en blev som følgende:

1
2
3
4

SELECT name FROM item
WHERE EXISTS (SELECT imageid FROM image
LEFT JOIN items_and_images AS iai USING(imageid)
WHERE iai.itemid = item.itemid);

Hvilket gav navnene: Alfa, Beta og Charlie :-)

Fidusen ved EXISTS er, at den returnerer true hvis dens argument returnerer rækker. Argumentet til EXISTS skal derfor være en subselection.

Next Page »

• Seneste indlæg

  • Fra PDF til PNG med PHP på Windows
  • Streaming med flowplayer
  • Sådan fik jeg FFmpeg til at lave H.264
  • Undgå at folk linker til billeder
  • Sortering på tabeller
  • Komprimer dit site og øg hastigheden
  • Spore hvilke links brugeren trykker på
  • Sprog i Smarty
  • Sprogstyring via mod_rewrite
  • E_NOTICE i PHP
  • Få styr på ens domæne
  • “If-sætninger” i Apaches mod_rewrite
  • Konvertere links til posts (Fra GET til POST)
  • Et lille jQuery tip
  • Kommentarer i MySQL-forespørgsler

• Seneste kommentarer

  • Morten Blinksbjerg Nielsen on Sortering på tabeller
  • Andreas on Sortering på tabeller
  • www.anyhed.dk on Spore hvilke links brugeren trykker på
  • Morten Blinksbjerg Nielsen on Spore hvilke links brugeren trykker på
  • Brian Nørremark on Spore hvilke links brugeren trykker på

• Tags

  apache BedsteVen.dk billeder caching combatclinic.com excel excel2007 exists fulltext htaccess html ie image_graph insert internet explorer javascript jquery limit links mail max_execution_time microsoft mime mod_expires mod_rewrite mysql nyhedsbrev office pear php rewrite smartlog smarty sprog stored procedure ugenumre untitled.bmp update video view views week word word2007 zip

• 
  

• Om websmed.dk

  Jeg hedder Morten, og jeg har udviklet webapplikationer siden slutningen af 90'erne.
  
  Jeg vil her dele ud af min erfaring, og med jævne mellemrum poste nogle tips og tricks, samt løsningsforslag på generelle dagligdags problemstillinger.
  
  Jeg har en forkærlighed for PHP og MySQL, hvorfor I nok vil se flest indlæg der vedrører den gren af webudvikling.
  
  Jeg har en anden blog af mere personlig karakter på mbn.dk, hvor I kan finde kontaktmuligheder m.m.
  
  Mine indlæg vil tit være baseret på problemstillinger i mit daglige virke, eller i mit eget firma MRLYTICS, hvor jeg sælger kundeundersøgelser samt kundedatabaser.
  
  Denne side er hostet hos slicehost.

  

• Skrevet på Twitter