English
Tag "mysql"
16:03
Szybkość PDO w PHP
Postanowiłem sprawdzić na własnej skórze jak to jest z tym PDO dla PHP. Przypomnę, że PDO jest uniwersalnym interfejsem do łączenia się z bazami danych. W moich testach porównywałem szybkość pobierania danych przez PDO kontra funkcje mysql dla połączenia z bazą MySQL. A wyniki są następujące:
SELECT dla bazy o 10 rekordach (wykonane 10tyś razy):
- old mysql: 0.254 sek
- pdo: 0.756 sek.
SELECT dla bazy o 100 rekordach (wykonane 10tyś razy):
- old mysql: 10.589 sek.
- pdo: 12.284 sek.
SELECT dla bazy o 500 rekordach (wykonane 1tyś razy):
- old mysql: 5.514 sek.
- pdo: 3.739 sek.
SELECT dla bazy o ponad 100tyś. Rekordów (wykonane 5 razy):
- old mysql: 5.559 sek.
- pdo: 3.024 sek.
Wnioski:
PDO jest szybsze od wbudowanych w php funkcji do łączenia się z bazą MySQL, ale tylko dla tabel, które zawierają powyżej 150 rekordów. Niemniej chyba nawet małe serwisy internetowe przekraczają tą liczbę, więc polecam stosować PDO we wszystkich projektach PHP, wymagających połączenia z bazą danych – bo szybkość, to nie jedyna korzyść płynąca z używania tego interfejsu.
16:17
MySQL - funkcje przetwarzania daty
Funkcje podstawowe
Pobieranie aktualnej daty: CURDATE(), lub CURRENT_DATE. Funkcje wypisują aktualną datę na serwerze bazy danych MySQL w formacie: RRRR-MM-DD. W przypadku użycia polecenia SELECT CURDATE(); otrzymamy datę 2006-12-13
Pobieranie aktualnego czasu: CURTIME(), lub CURRENT_TIME. Funkcja wypisuje aktualny czas na serwerze bazy danych MySQL w formacie HH:MI:SS. W przypadku użycia polecenia SELECT CURTIME(); otrzymamy czas 00:16:24
Pobieranie aktualnej daty i czasu: NOW(), SYSDATE(), lub CURRENT_TIMESTAMP. Funkcje te zwracają aktualną datę formacie: RRRR-MM-DD HH:MI:SS. W Przypadku użycia polecenia SELECT NOW(); otrzymamy 2006-12-13 00:21:49
Dodawanie interwałów do dat: DATE_ADD(data, INTERVAL jednostki). Funkcja ta dodaje do podanej daty określoną liczbę jednostek. W Przypadku użycia polecenia SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); otrzymamy aktualną datę z dniem zwiększonym o 1.
Odejmowanie interwałów od dat: DATE_SUB(data, INTERVAL jednostki). Funkcja ta odejmie od podanej daty określoną liczbę jednostek. W przypadku użycia polecenia SELECT DATE_SUB(NOW(), INTERVAL 1 DAY); otrzymamy aktualną datę z dniem mniejszym o 1.
Składowe daty
Istnieje kilkanaście funkcji pozwalających pobrać określone składowe daty.
- DAYOFWEEK(data) – zwraca dzień tygodnia w postaci cyfry
- WEEKDAY(data) – zwraca dzień tygodnia w postaci cyfry (0 - Poniedziałek)
- DAYOFMONTH(data) – zwraca dzień miesiąca w postaci cyfry
- DAYOFYEAR(data) – zwraca dzień w roku w postaci cyfry
- MONTH(data) – zwraca numer miesiąca w roku
- DAYNAME(data) – zwraca pełną nazwę dnia tygodnia
- MONTHNAME(data) – zwraca pełną nazwę miesiąca
- QUARTER(data) – zwraca numer kwartału
- WEEK(data) – zwraca numer tygodnia w roku
- YEAR(data) – zwraca rok
- HOUR(data) – zwraca godzinę
- MINUTE(data) – zwraca liczbę minut
- SECOND(data) – zwraca liczbę sekund
- TO_DAYS(data) – zwraca liczbę dni, które minęły od początku naszej ery
- FROM_DAYS(dni) – zwraca datę, którą wskazuje liczba dni od początku naszej ery
Przekształcanie formatu daty
Do przekształcania formatu zwracanej przez MYSQL daty służą funkcja DATE_FORMAT(data,format). Przykład użycia: SELECT DATE_FORMAT(NOW(),'%d %M %Y'); zwróci datę w formacie 13 December 2006. Poniżej znajduje się pełna lista znaczników formatu daty dla MySQL:
- %a – zwraca skróconą nazwę dnia tygodnia
- %b – zwraca skróconą nazwę miesiąca
- %c – zwraca numer miesiąca
- %D – zwraca dzień miesiąca wraz z liczebnikiem porządkowym
- %d – zwraca dzień miesiąca w formacie dwucyfrowym
- %e – zwraca dzień miesiąca
- %f – zwraca liczbę mikrosekund
- %H – zwraca godzinę w formacie 24 godzinnym, dwucyfrowym
- %h – zwraca godzinę w formacie 12 godzinnym, dwucyfrowym
- %i – zwraca liczbę minut
- %j – zwraca numer dnia w roku w formacie trzycyfrowym
- %k – zwraca godzinę w formacie 24 godzinnym
- %l – zwraca godzinę w formacie 12 godzinnym
- %M – zwraca pełną nazwę miesiąca
- %m – zwraca numer miesiąca
- %p – zwraca AM, lub PM w zależności od pory dani
- %r – zwraca czas w formacie 12 godzinnym
- %S – zwraca liczbę sekund
- %T – zwraca czas w formacie 24 godzinnym
- %U – zwraca numer tygodnia (niedziela jest dniem pierwszym, liczone od 00)
- %u – zwraca numer tygodnia (poniedziałek jest dniem pierwszym, liczone od 00)
- %V – zwraca numer tygodnia (niedziela jest pierwszym dniem, liczone od 01)
- %v – zwraca numer tygodnia (poniedziałek jest pierwszym dniem, liczone od 01)
- %W – zwraca pełną nazwę dnia tygodnia
- %X – zwraca rok danego tygodnia (niedziela jest pierwszym dniem)
- %x – zwraca rok danego tygodnia (poniedziałek jest pierwszym dniem)
- %Y – zwraca rok w formacie czterocyfrowym
- %y – zwraca rok w formacie dwucyfrowym
Kilka przykładów zastosowania
Powyższe wywołania funkcji nie znajdą jednak większego zastosowania w operacjach na bazie danych. Najczęstsze zastosowanie mają one przy warunku WHERE. Pozwalają one precyzyjnie określić, które dane mają zostać pobrane z bazy, zaktualizowane lub usunięte. Zastosowanie również mają przy dodawaniu nowych rekordów do bazy.
Dodawanie nowych danych z aktualną datą:
INSERT INTO test.test (vol,data) VALUES('aaa',NOW());
Dodawanie nowych danych z jutrzejszą data:
INSERT INTO test.test (vol,data) VALUES('aaa',DATE_ADD(NOW(),INTERVAL 1 DAY));
Dodawanie nowych danych z datą sprzed roku:
INSERT INTO test.test (vol,data) VALUES('aaa',DATE_SUB(NOW(),INTERVAL 1 YEAR));
Pobieranie danych starszych, niż 1 rok:
SELECT * FROM test.test WHERE test.data < DATE_SUB(NOW(),INTERVAL 1 YEAR);
Pobieranie danych z przedziału jutro-wczoraj:
SELECT * FROM test.test WHERE test.data > DATE_SUB(NOW(),INTERVAL 1 DAY) AND test.data < DATE_ADD(NOW(),INTERVAL 1 DAY);
Pobieranie danych z wczorajszą datą:
SELECT * FROM test.test WHERE test.data LIKE CONCAT(DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y-%m-%d'),'%');
Wyświetlanie samej daty, bez czasu:
SELECT id, DATE_FORMAT(data,'%Y-%m-%d') as data FROM test.test;
Zwiększanie daty o 1 rok:
UPDATE test.test SET data = DATE_ADD(data, INTERVAL 1 YEAR) WHERE id='1';
Obliczanie różnicy dni między aktualną datą, a wybraną:
SELECT id,TO_DAYS(NOW()) - TO_DAYS(data) as roznica FROM test.test;
Zwracanie dnia tygodnia w postaci:
SELECT id,DAYNAME(data) as dzien_tygodnia FROM test.test;
15:40
MySQL - Wyrażenia regularne
Metaznaki wyrażeń regularnych dla MySQL
Mimo, że wiele języków umożliwia używanie wyrażeń regularnych to te znaki mogą być różne dla danych języków programowania. Poniżej znajduje się lista znaków dla MySQL.
.- dowolny znak^- początek ciągu$- koniec ciągu[...]- dowolny znak ze zbioru (...)[^...]- dowolny znak spoza zbioru (...)[:k:]- dowolny znak z danej klasy ka*- liczba wystąpień znaku a wynosi zero lub więceja+- co najmniej jedno wystąpienie znaki aa?- co najwyżej jedno wystąpienie znaku aa{5}– pięciokrotne wystąpienie znaku aa{1,5}– wystąpienie znaku a od jednego do pięciu razya{5,}- wystąpienie znaku a co najmniej 5 razya|b– wystąpienie znaku a lub b(...)- definicja wyrażenia składowego
Aby poinformować MySQL o używaniu w zapytaniu wyrażeń regularnych używa się polecenia REGEXP. MySQL nie rozróżnia wielkości znaków w wewnątrz zapytania, więc decyzja czy używać małe, czy wielkie litery podczas tworzenia zapytania należy do użytkownika. Bywają też sytuacje, kiedy znak do wyszukania stanowi część wyrażenia regularnego (np. wyszukanie znaku [). W takim wypadku przed użyciem danego symbolu należy dodać (np. [).
Przykłady użycia wyrażeń regularnych
Stosowanie wyrażeń regularnych nie jest trudne, jak to się często początkującym użytkownikom wydaje. Poniżej prezentuję kilka zagadnień związanych z zapytaniami MySQL z wyrażeniami regularnymi.
Wyszukanie rekordów kończących się ciągiem "a";
SELECT id,txt1 FROM test.test WHERE txt1 REGEXP 'a$';
Wyszukiwanie rekordów zaczynających się od cyfry
SELECT id,txt1 FROM test.test WHERE txt1 REGEXP '^[:0-9:]';
Wyszukanie rekordów zawierających ciąg "jak"
SELECT id,txt1 FROM test.test WHERE txt1 REGEXP 'jak';
Wyszukiwanie rekordów, w których pierwszy wyraz zaczyna się na s
SELECT id,txt2 FROM test.test WHERE txt2 REGEXP '^([[:<:]]s)';
Wyszukiwanie rekordów, dłuższych niż jeden wyraz
SELECT id,txt2 FROM test.test WHERE txt2 REGEXP '^.+ ';
Wyszukiwanie rekordów z adresami stron internetowych
SELECT id,txt1 FROM test.test WHERE txt1 REGEXP '(http://)+[a-z0-9]+(.[a-z0-9])*(.[a-z])';
Wyszukiwanie rekordów zawierających adres email
SELECT id,txt1 FROM test.test WHERE txt1 REGEXP '([a-z0-9]+@{1}[a-z0-9]+(.[a-z][0-9])*(.[a-z]))';
23:04
MySQL - Transakcje
Efekty przeprowadzanej transakcji można obserwować na bieżąco poprzez instrukcje SELECT. W przypadku stwierdzenia jakiejś nieprawidłowości możliwe jest cofnięcie się do miejsca, w którym transakcja została zapisana, lub całkowite przerwanie transakcji. Baza MySQL obsługuje transakcje stosunkowo od niedawna. Domyślnie stosowany typ tabeli MyISAM nie miał możliwości przeprowadzania transakcji. Funkcjonalność ta pojawiła się dopiero po wprowadzeniu tabel InnoDB.
Przygotowanie tabeli
Przed rozpoczęciem transakcji trzeba odpowiednio przygotować tabelę w bazie danych. Tabela, na której będziemy przeprowadzać operacje powinna być typu InnoDB. Aktualnie jest to jedyny (obok nie w pełni zintegrowanego BerkeleyDB) typ tabeli w MySQL, który obsługuje transakcje. Poniżej znajduje się przykładowa struktura tabeli, na której będziemy przeprowadzać operacje.
CREATE TABLE `trans` ( `id` INT NOT NULL AUTO_INCREMENT , `re` INT NOT NULL , `txt` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) ) TYPE = innodb;
Rozpoczynanie transakcji
Aby rozpocząć transakcję w bazie MySQL należy posłużyć się poleceniem START TRANSACTION; Przed rozpoczęciem transakcji można również określić poziom izolacji za pomocą polecenia SET TRANSACTION ISOLATION LEVEL. Dla tego polecenia dostępne są cztery parametry: { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }. Jeżeli nie użyjemy polecenia SET TRANSACTION, zostanie jako domyślnie użyta opcja REPEATABLE READ. Poniżej przykład rozpoczęcia transakcji:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION;
Zapisywanie stanu transakcji
Zapisywanie stanu transakcji nie jest krokiem koniecznym. Wykorzystuje się je w przypadku, gdy podczas przeprowadzania transakcji mogą wystąpić jakieś problemy. Wtedy jest możliwość cofnięcia się do wcześniej zapisanego stanu. Zapisania stanu transakcji dokonuje się poleceniem SAVEPOINT punkt; gdzie punkt jest nazwą zapisywanej pozycji.
Kończenie transakcji
Do zakończenia aktualnej transakcji używa się polecenia COMMIT;. Po zakończeniu transakcji wszystkie przeprowadzone operacje są zapisywane do bazy danych, bez możliwości jej odwołania.
Przerwanie transakcji
Są jednak przypadki, kiedy jesteśmy zmueszni z jakiegoś powodu przerwać transakcję. Do tego celu służy polecenie ROLLBACK; W przypadku jego użycia wszystkie wydane polecenia podczas trwania transakcji zostaną odwołane i zawartość bazy nie ulegnie zmianie. Za pomocą tego polecenia możliwe jest też cofnięcie się do wcześniej zapisanego stanu transakcji. W tym wypadku polecenie będzie wyglądało następująco: ROLLBACK TO punkt; gdzie punkt jest nazwą ostatnio zapisanej pozycji.
Przykłady przebiegu kilku transakcji:
Transakcja 1:
START TRANSACTION; INSERT INTO trans (re,txt) VALUES (0,'rek 1'); INSERT INTO trans (re,txt) VALUES (0,'rek 2');
Podgląd stanu tabeli w przypadku pomyślnego zakończenia transakcji:
SELECT * FROM trans; +----+----+-------+ | id | re | txt | +----+----+-------+ | 1 | 0 | rek 1 | | 2 | 0 | rek 2 | +----+----+-------+
COMMIT;
Tu nastąpiło zakończenie transakcji – dodane rekordy znalazły się w bazie.
Transakcja 2:
START TRANSACTION; INSERT INTO trans (re,txt) VALUES (5,'trans 2');
Podgląd stanu transakcji:
SELECT * FROM trans; +----+----+---------+ | id | re | txt | +----+----+---------+ | 1 | 0 | rek 1 | | 2 | 0 | rek 2 | | 3 | 5 | trans 2 | +----+----+---------+
ROLLBACK;
Tutaj nastąpiło przerwanie transakcji. Aby pokazać, że wpis nie został dodany do bazy wywołujemy ponownie komendę:
SELECT * FROM trans; +----+----+-------+ | id | re | txt | +----+----+-------+ | 1 | 0 | rek 1 | | 2 | 0 | rek 2 | +----+----+-------+
Transakcja 3:
START TRANSACTION; UPDATE trans SET re=5 WHERE id=2; SAVEPOINT krok1; INSERT INTO trans (re,txt) VALUES (5,'trans 3');
Sprawdzenie stanu transakcji:
SELECT * FROM trans; +----+----+---------+ | id | re | txt | +----+----+---------+ | 1 | 0 | rek 1 | | 2 | 5 | rek 2 | | 4 | 5 | trans 3 | +----+----+---------+
ROLLBACK TO krok1; COMMIT;
Transakcja została pomyślenie zakończono, jednak przed jej zakończeniem cofnęliśmy się do wcześniej zapisanego stanu transakcji. W wyniku tego polecenie dodania nowego rekordu zostało odrzucone:
SELECT * FROM trans; +----+----+-------+ | id | re | txt | +----+----+-------+ | 1 | 0 | rek 1 | | 2 | 5 | rek 2 | +----+----+-------+
Nazywam się Tomasz Chudyk i witam na mojej stronie. Jestem studentem piątego roku informatyki. Moje zainteresowania krążą głównie wokół open-source, Linuksa i technologi internetowych.
