{"id":226023,"date":"2022-08-17T13:19:00","date_gmt":"2022-08-17T10:19:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=226023"},"modified":"2022-11-08T01:53:17","modified_gmt":"2022-11-07T22:53:17","slug":"mysql-i-tabeliloigud-ja-levinud-probleemide-parandused","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/et\/mysql-i-tabeliloigud-ja-levinud-probleemide-parandused\/","title":{"rendered":"MySQL-i tabelil\u00f5igud ja levinud probleemide parandused"},"content":{"rendered":"\n

MySQL-i tabelijupid?<\/p>\n

Jah, selles artiklis on neid \u00fcsna palju. Aga enne selle juurde j\u00f5udmist, mis on MySQL? Alustame MySQL-i m\u00e4\u00e4ratlusega. MySQL on Oracle’i toetatud avatud l\u00e4htekoodiga relatsioonilise andmebaasi halduss\u00fcsteem (RDBMS), mis p\u00f5hineb struktureeritud p\u00e4ringukeelel (SQL).<\/p>\n

MySQL-i andmebaasi kasutavad rakendused on TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB ja Drupal. MySQL-i kasutatakse ka paljudel k\u00f5rgetasemelistel suuremahulistel veebisaitidel, sealhulgas Google’is (kuigi mitte otsingute jaoks), Facebookis, Twitteris, Flickris ja YouTube’is.<\/p>\n

Ja p\u00e4rast kogu seda sissejuhatust m\u00f5istate, et MySQL on suurem, kui alguses arvasite. Kui te pole selles valdkonnas m\u00f5nda aega tegutsenud. Kui olete aga uustulnuk ja soovite MySQL-i kohta rohkem teada saada, on lihtsaim viis seda teha MySQL-i tabelijuppidega.<\/p>\n

MySQL-i tabelijupid<\/h2>\n

Sobitage string stringi alguses<\/strong><\/p>\n

SELECT 'Test' REGEXP '^The'; -- 0 SELECT 'The Test' REGEXP '^The'; -- 1<\/code><\/p>\n

Kui nime ees ei ole ‘The’, siis lisage see<\/strong><\/p>\n

UPDATE [table] SET Name = CONCAT('The ', TRIM(Name)) WHERE Name NOT REGEXP '^The'<\/code><\/p>\n

Kopeerige veerg \u00fchest tabelist teise<\/strong><\/p>\n

INSERT INTO [table] (<\/code><\/p>\n

) SELECT FROM [table]<\/code><\/p>\n

Eemaldage k\u00f5ik t\u00fchikud<\/strong><\/p>\n

UPDATE [table] SET<\/code><\/p>\n

= REPLACE(, ' ', '')<\/code><\/p>\n

(see on BASH) kukutage k\u00f5ik andmebaasi tabelid<\/strong><\/p>\n

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]<\/code><\/p>\n

Tabeli ja k\u00f5igi selle olemasolevate veergude v\u00f5rdlemise muutmine<\/strong><\/p>\n

alter table [table] convert to character set utf8 collate utf8_general_ci;<\/code><\/p>\n

MySQL Otsi ja asenda<\/strong><\/p>\n

UPDATE files SET filepath = REPLACE(filepath,'path\/to\/search','path\/to\/replace');<\/code><\/p>\n

Juhusliku rea valimine MySQL-is<\/strong><\/p>\n

SELECT column FROM table ORDER BY RAND() LIMIT 1<\/code><\/p>\n

Looge MySQL-ist CSV<\/strong><\/p>\n

SELECT a,b,c FROM my_table INTO OUTFILE '\/ca.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n';<\/code><\/p>\n

Otsige \u00fcles topeltkirjed<\/strong><\/p>\n

SELECT id, COUNT(*) as n FROM my_table GROUP BY id HAVING n >1;<\/code><\/p>\n

Vale m\u00e4rgikodeeringu parandamine MySQL-i andmetes<\/strong><\/p>\n

UPDATE table SET column=CONVERT(CONVERT(CONVERT(column USING latin1) USING binary) using utf8);<\/code><\/p>\n

Laadige CSV tagasi mySQL-i<\/strong><\/p>\n

LOAD DATA local INFILE 'unique.csv' INTO TABLE tablename FIELDS TERMINATED BY ',' LINES TERMINATED BY 'n' (col1, col2, col3)<\/code><\/p>\n

Drupal 6.x Otsige andmebaasist PHP-koodi<\/strong><\/p>\n

select nid, vid from node_revisions where format in (select format from filters where module = 'php' and delta = 0); select cid, nid from comments where format in (select format from filters where module = 'php' and delta = 0); select bid, info from boxes where format in (select format from filters where module = 'php' and delta = 0);<\/code><\/p>\n

Looge MySQL-is juhuslik string<\/strong><\/p>\n

SELECT SUBSTRING(MD5(RAND()) FROM 1 FOR 6) AS randomstring<\/code><\/p>\n

Arvutage vanus s\u00fcnnikuup\u00e4evast<\/strong><\/p>\n

SELECT FLOOR(DATEDIFF(CURRENT_DATE(), dob) \/ 365);<\/code><\/p>\n

Otsi ja asenda<\/strong><\/p>\n

UPDATE table_name SET column_name = REPLACE(column_name, 'http:\/\/oldcontent.com', 'http:\/\/newcontent.com');<\/code><\/p>\n

Valige kahe kuup\u00e4eva vahel<\/strong><\/p>\n

SELECT * FROM files LEFT JOIN (dates) ON files.uploaded_id = dates.id WHERE date BETWEEN '2009-01-01' AND '2009-12-31'<\/code><\/p>\n

Varundage MySQL-i andmebaas SQL-faili rakendusega mysqldump<\/strong><\/p>\n

mysqldump --user=username --password=password --opt --add-drop-database --databases databasename > ~\/path\/to\/file.sql<\/code><\/p>\n

kuidas arvutada viimast 7 p\u00e4eva<\/strong><\/p>\n

WHERE mydatefld >= DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)<\/code><\/p>\n

mysql dump<\/strong><\/p>\n

mysqldump -u [username] -p [password] [databasename] > [backupfile.sql]<\/code><\/p>\n

Mysql asendamine<\/strong><\/p>\n

`# Asenda string k\u00f5igis tabeli kirjetes
\nUPDATE tabeli_nimi SET veeru_nimi = REPLACE( veeru_nimi, ‘\/dev’, " );<\/p>\n

SELECT REPLACE( veeru_nimi, ‘\/dev’, “) FROM tabeli_nimi;`<\/p>\n

Teisendage ajav\u00f6\u00f6nd kohalikuks<\/strong><\/p>\n

SELECT CONVERT_TZ(mydatefield,'US\/Pacific','CET') FROM mytable;<\/code><\/p>\n

Otsige ja asendage mitmel real SQL-is<\/strong><\/p>\n

UPDATE wp_posts SET post_content = REPLACE(post_content, 'staging.server.com', 'www.productionserver.com');<\/code><\/p>\n

Kuidas salvestada MySQL-i tulemusi v\u00e4lisesse faili<\/strong><\/p>\n

echo \"SELECT columns FROM table_name WHERE whatever='something'\" | \/path\/to\/mysql -uUSERNAME -pPASSWORD DATABASENAME > \/tmp\/outfile.txt;<\/code><\/p>\n

Sisestage v\u00f5i v\u00e4rskendage, kui kordumatu v\u00f5ti on juba olemas<\/strong><\/p>\n

INSERT INTO `tableName` VALUES (?,?,?,'-1',?,'0') ON DUPLICATE KEY UPDATE `id` = ?, `foo` = ?, `bar` = ?<\/code><\/p>\n

mySQL \u2013 andmebaasi varundamise k\u00e4sud<\/strong><\/p>\n

-- dump mySQL database to file: VAR=$(date +\u00c3\u00a2\u00ef\u00bf\u00bd\u00ef\u00bf\u00bd%y-%m-%d\u00c3\u00a2\u00ef\u00bf\u00bd\u00ef\u00bf\u00bd); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > \/BACKUPDIR\/$VAR-DATABASENAME.sql ;<\/code><\/p>\n

-- dump mySQL database to file and then move it: VAR=$(date +\u00c3\u00a2\u00ef\u00bf\u00bd\u00ef\u00bf\u00bd%y-%m-%d\u00c3\u00a2\u00ef\u00bf\u00bd\u00ef\u00bf\u00bd); mysqldump -v -uUSERNAME -pPASSWORD -hlocalhost DATABASENAME > \/BACKUPDIR\/$VAR-DATABASENAME.sql ; mv \/BACKUPDIR\/DATABASENAME.sql \/OTHERBACKUPDIR\/$VAR-DATABASENAME.sql ;<\/code><\/p>\n

MySql import<\/strong><\/p>\n

mysql -u [username] -p [dbname] < [filename].sql<\/code><\/p>\n

leida topeltkirjeid<\/strong><\/p>\n

select address, count(address) as cnt from mailing_list group by address having cnt > 1 order by cnt;<\/code><\/p>\n

MySQL Otsi ja asenda<\/strong><\/p>\n

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, 'find this string', 'replace found string with this string');<\/code><\/p>\n

Muutke Drupali faili kataloogi teed<\/strong><\/p>\n

UPDATE FILES SET filepath = REPLACE(filepath, 'sites\/default\/files\/old\/','sites\/default\/files\/new\/');<\/code><\/p>\n

Otsige MySQL andmebaasist \u00fcles k\u00f5ik tabelid, mis sisaldavad konkreetseid veergude nimesid<\/strong><\/p>\n

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('columnA','ColumnB') AND TABLE_SCHEMA='YourDatabase';<\/code><\/p>\n

Otsige tabelist v\u00e4\u00e4rtused, mis esinevad t\u00e4pselt \u00fcks kord<\/strong><\/p>\n

SELECT email FROM users GROUP BY email HAVING (COUNT(email) = 1 )<\/code><\/p>\n

MySql skript kehtetute e-posti aadresside leidmiseks<\/strong><\/p>\n

SELECT * FROM people WHERE email NOT LIKE '%_@__%.__%'<\/code><\/p>\n

MySQL-i vanuse arvutamine<\/strong><\/p>\n

DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DOB, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DOB, '00-%m-%d')) >= 65<\/code><\/p>\n

Vasakpoolne liitmine kolme v\u00f5i enama tabeli linkimiseks<\/strong><\/p>\n

select * from (bdg left join res on bdg.bid = res.bid) left join dom on res.rid = dom.rid where dom.rid is NULL;<\/code><\/p>\n

MySQL REGEX vaste komadega eraldatud loend<\/strong><\/p>\n

WHERE id REGEXP '(,|^){$this->id}(,|$)'<\/code><\/p>\n

Hankige kaalu p\u00f5hjal juhuslik rekord<\/strong><\/p>\n

SELECT * FROM Table ORDER BY Rand() * (1 \/ Weight<\/code><\/p>\n

Loodame, et need MySQL-i tabelil\u00f5igud on see, mida otsisite. V\u00e4rskendame seda artiklit teiste MySQL-i fragmentidega, millega tulevikus kokku puutume, seega lisage see kindlasti j\u00e4rjehoidjatesse.<\/p>\n

Kui teile meeldis lugeda seda artiklit MySQL-i tabelijuppide kohta, peaksite lugema ka j\u00e4rgmist:<\/p>\n