{"id":225913,"date":"2022-08-17T13:54:00","date_gmt":"2022-08-17T10:54:00","guid":{"rendered":"https:\/\/wordpress.mediadoma.com\/?p=225913"},"modified":"2022-11-08T01:19:13","modified_gmt":"2022-11-07T22:19:13","slug":"mysql-tabellsnuttar-och-korrigeringar-av-vanliga-problem","status":"publish","type":"post","link":"https:\/\/wordpress.mediadoma.com\/sv\/mysql-tabellsnuttar-och-korrigeringar-av-vanliga-problem\/","title":{"rendered":"MySQL-tabellsnuttar och korrigeringar av vanliga problem"},"content":{"rendered":"\n

MySQL-tabellsnuttar?<\/p>\n

Ja, det finns en hel del av dem i den h\u00e4r artikeln. Men innan vi kommer till det, vad \u00e4r MySQL? L\u00e5t oss b\u00f6rja med MySQL-definitionen. MySQL \u00e4r ett Oracle-st\u00f6dt open source relationsdatabashanteringssystem (RDBMS) baserat p\u00e5 Structured Query Language (SQL).<\/p>\n

Applikationer som anv\u00e4nder MySQL-databasen inkluderar TYPO3, MODx, Joomla, WordPress, Simple Machines Forum, phpBB, MyBB och Drupal. MySQL anv\u00e4nds ocks\u00e5 p\u00e5 m\u00e5nga h\u00f6gprofilerade, storskaliga webbplatser, inklusive Google (men inte f\u00f6r s\u00f6kningar), Facebook, Twitter, Flickr och YouTube.<\/p>\n

Och efter allt det d\u00e4r introt inser du att MySQL \u00e4r st\u00f6rre \u00e4n du f\u00f6rst trodde. Om du inte har funnits ett tag i branschen. Om du d\u00e4remot \u00e4r en nykomling och vill l\u00e4ra dig mer om MySQL \u00e4r det enklaste s\u00e4ttet att g\u00f6ra det med MySQL-tabellsnuttar.<\/p>\n

MySQL-tabellsnuttar<\/h2>\n

Matcha en str\u00e4ng i b\u00f6rjan av str\u00e4ngen<\/strong><\/p>\n

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

Om ett namn inte har prefixet ’The’, l\u00e4gg till det<\/strong><\/p>\n

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

Kopiera en kolumn fr\u00e5n en tabell till en annan<\/strong><\/p>\n

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

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

Ta bort alla blanksteg<\/strong><\/p>\n

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

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

(detta \u00e4r BASH) sl\u00e4pp alla tabeller i en databas<\/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

\u00c4ndra till sortering av en tabell och alla dess befintliga kolumner<\/strong><\/p>\n

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

MySQL S\u00f6k och ers\u00e4tt<\/strong><\/p>\n

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

V\u00e4lja en slumpm\u00e4ssig rad i MySQL<\/strong><\/p>\n

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

Skapa CSV fr\u00e5n MySQL<\/strong><\/p>\n

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

Hitta dubbletter av poster<\/strong><\/p>\n

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

R\u00e4ttar fel teckenkodning i MySQL-data<\/strong><\/p>\n

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

Ladda tillbaka CSV till mySQL<\/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 Hitta PHP-kod i databasen<\/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

Generera slumpm\u00e4ssig str\u00e4ng i MySQL<\/strong><\/p>\n

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

Ber\u00e4kna \u00e5lder fr\u00e5n f\u00f6delsedatum<\/strong><\/p>\n

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

Hitta & ers\u00e4tt<\/strong><\/p>\n

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

V\u00e4lj mellan tv\u00e5 datum<\/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

S\u00e4kerhetskopiera MySQL-databas till SQL-fil med mysqldump<\/strong><\/p>\n

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

hur man ber\u00e4knar de senaste 7 dagarna<\/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 ers\u00e4tt<\/strong><\/p>\n

`# Ers\u00e4tt str\u00e4ng i alla poster fr\u00e5n en tabell
\nUPPDATERA tabellnamn SET kolumnnamn = REPLACE( kolumnnamn, ’\/dev’, " );<\/p>\n

SELECT REPLACE( kolumnnamn, ’\/dev’, ”) FR\u00c5N tabellnamn;`<\/p>\n

Konvertera tidszon till lokal<\/strong><\/p>\n

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

S\u00f6k och ers\u00e4tt p\u00e5 flera rader i SQL<\/strong><\/p>\n

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

Hur man sparar resultat fr\u00e5n MySQL till en extern fil<\/strong><\/p>\n

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

Infoga eller uppdatera om unik nyckel redan finns<\/strong><\/p>\n

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

mySQL \u2013 kommandon f\u00f6r s\u00e4kerhetskopiering av databas<\/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

hitta dubbletter av poster<\/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 S\u00f6k och ers\u00e4tt<\/strong><\/p>\n

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

\u00c4ndra s\u00f6kv\u00e4gen till Drupal-filkatalogen<\/strong><\/p>\n

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

Hitta alla tabeller i MySQL-databasen som inneh\u00e5ller specifika kolumnnamn<\/strong><\/p>\n

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

Hitta v\u00e4rden som f\u00f6rekommer exakt en g\u00e5ng i tabellen<\/strong><\/p>\n

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

MySql-skript f\u00f6r att hitta ogiltiga e-postadresser<\/strong><\/p>\n

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

MySQL \u00e5ldersber\u00e4kning<\/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

Left Joins f\u00f6r att l\u00e4nka tre eller fler tabeller<\/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 matchar kommaavgr\u00e4nsad lista<\/strong><\/p>\n

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

F\u00e5 slumpm\u00e4ssigt rekord baserat p\u00e5 vikt<\/strong><\/p>\n

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

Vi hoppas att dessa MySQL-tabellsnuttar \u00e4r det du letade efter. Vi kommer att uppdatera den h\u00e4r artikeln med andra MySQL-snuttar som vi st\u00f6ter p\u00e5 i framtiden, s\u00e5 se till att bokm\u00e4rka den.<\/p>\n

Om du tyckte om att l\u00e4sa den h\u00e4r artikeln om MySQL-tabellsnuttar, b\u00f6r du ocks\u00e5 l\u00e4sa dessa:<\/p>\n