How does Python crawl JD product information and comments and store them into MySQL?-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

How does Python crawl JD product information and comments and store them into MySQL?

PHPz

May 26, 2023 pm 07:58 PM

mysqlpython

Build mysql data table

Question: When using SQL alchemy, non-primary keys cannot be set to auto-increment, but I want this non-primary key to be used only as an index, autoincrement=True Invalid, how to make it grow automatically?

from sqlalchemy import String,Integer,Text,Column
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.orm import scoped_session
from sqlalchemy.ext.declarative import declarative_base
 
engine=create_engine(
    "mysql+pymysql://root:root@127.0.0.1:3306/jdcrawl?charset=utf8",
    pool_size=200,
    max_overflow=300,
    echo=False
)
 
BASE=declarative_base() # 实例化
 
class Goods(BASE):
    __tablename__=&#39;goods&#39;
    id=Column(Integer(),primary_key=True,autoincrement=True)
    sku_id = Column(String(200), primary_key=True, autoincrement=False)
    name=Column(String(200))
    price=Column(String(200))
    comments_num=Column(Integer)
    shop=Column(String(200))
    link=Column(String(200))
 
class Comments(BASE):
    __tablename__=&#39;comments&#39;
    id=Column(Integer(),primary_key=True,autoincrement=True,nullable=False)
    sku_id=Column(String(200),primary_key=True,autoincrement=False)
    comments=Column(Text())
 
BASE.metadata.create_all(engine)
Session=sessionmaker(engine)
sess_db=scoped_session(Session)

First version:

Problem:After crawling a few pages of comments, a blank page will be crawled. This is still the case after adding a refer

Try the solution: Change the thread pool where comments are obtained to a single thread, and increase the delay by 1s for each page of comments

# 不能爬太快！！！不然获取不到评论
 
from bs4 import BeautifulSoup
import requests
from urllib import parse
import csv,json,re
import threadpool
import time
from jd_mysqldb import Goods,Comments,sess_db
 
headers={
    &#39;user-agent&#39;: &#39;Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36&#39;,
    &#39;Cookie&#39;: &#39;__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918&#39;,
    &#39;Referer&#39;: &#39;https://www.jd.com/&#39;
}
 
num=0   # 商品数量
comments_num=0   # 评论数量
 
# 获取商品信息和SkuId
def getIndex(url):
    session=requests.Session()
    session.headers=headers
    global num
    res=session.get(url,headers=headers)
    print(res.status_code)
    res.encoding=res.apparent_encoding
    soup=BeautifulSoup(res.text,&#39;lxml&#39;)
    items=soup.select(&#39;li.gl-item&#39;)
    for item in items[:3]:  # 爬取3个商品测试
        title=item.select_one(&#39;.p-name a em&#39;).text.strip().replace(&#39; &#39;,&#39;&#39;)
        price=item.select_one(&#39;.p-price strong&#39;).text.strip().replace(&#39;￥&#39;,&#39;&#39;)
        try:
            shop=item.select_one(&#39;.p-shopnum a&#39;).text.strip()   # 获取书籍时查找店铺的方法
        except:
            shop=item.select_one(&#39;.p-shop a&#39;).text.strip()  #   获取其他商品时查找店铺的方法
        link=parse.urljoin(&#39;https://&#39;,item.select_one(&#39;.p-img a&#39;).get(&#39;href&#39;))
        SkuId=re.search(&#39;\d+&#39;,link).group()
        comments_num=getCommentsNum(SkuId,session)
        print(SkuId,title, price, shop, link, comments_num)
        print("开始存入数据库...")
        try:
            IntoGoods(SkuId,title, price, shop, link, comments_num)
        except Exception as e:
            print(e)
            sess_db.rollback()
        num += 1
        print("正在获取评论...")
        # 获取评论总页数
        url1 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10&#39;
        headers[&#39;Referer&#39;] = f&#39;https://item.jd.com/{SkuId}.html&#39;
        headers[&#39;Connection&#39;]=&#39;keep-alive&#39;
        res2 = session.get(url1,headers=headers)
        res2.encoding = res2.apparent_encoding
        json_data = json.loads(res2.text)
        max_page = json_data[&#39;maxPage&#39;]  # 经测试最多可获取100页评论，每页10条
        args = []
        for i in range(0, max_page):
            # 使用此链接获取评论得到的为json格式
            url2 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
            # 使用此链接获取评论得到的非json格式，需要提取
            # url2_2=f&#39;https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
            args.append(([session,SkuId,url2], None))
        pool2 = threadpool.ThreadPool(2)   # 2个线程
        reque2 = threadpool.makeRequests(getComments,args)  # 创建任务
        for r in reque2:
            pool2.putRequest(r) # 提交任务到线程池
        pool2.wait()
 
# 获取评论总数量
def getCommentsNum(SkuId,sess):
    headers[&#39;Referer&#39;]=f&#39;https://item.jd.com/{SkuId}.html&#39;
    url=f&#39;https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}&#39;
    res=sess.get(url,headers=headers)
    try:
        res.encoding=res.apparent_encoding
        json_data=json.loads(res.text)  # json格式转为字典
        num=json_data[&#39;CommentsCount&#39;][0][&#39;CommentCount&#39;]
        return num
    except:
        return &#39;Error&#39;
 
# 获取评论
def getComments(sess,SkuId,url2):
    global comments_num
    print(url2)
    headers[&#39;Referer&#39;] = f&#39;https://item.jd.com/{SkuId}.html&#39;
    res2 = sess.get(url2,headers=headers)
    res2.encoding=&#39;gbk&#39;
    json_data=res2.text
    &#39;&#39;&#39;
    # 如果用url2_2需要进行如下操作提取json
    start = res2.text.find(&#39;jQuery9287224(&#39;) + len(&#39;jQuery9287224(&#39;)
    end = res2.text.find(&#39;);&#39;)
    json_data=res2.text[start:end]
    &#39;&#39;&#39;
    dict_data = json.loads(json_data)
    try:
        comments=dict_data[&#39;comments&#39;]
        for item in comments:
            comment=item[&#39;content&#39;].replace(&#39;\n&#39;,&#39;&#39;)
            # print(comment)
            comments_num+=1
            try:
                IntoComments(SkuId,comment)
            except Exception as e:
                print(e)
                sess_db.rollback()
    except:
        pass
 
# 商品信息入库
def IntoGoods(SkuId,title, price, shop, link, comments_num):
    goods_data=Goods(
        sku_id=SkuId,
        name=title,
        price=price,
        comments_num=comments_num,
        shop=shop,
        link=link
    )
    sess_db.add(goods_data)
    sess_db.commit()
 
# 评论入库
def IntoComments(SkuId,comment):
    comments_data=Comments(
        sku_id=SkuId,
        comments=comment
    )
    sess_db.add(comments_data)
    sess_db.commit()
 
if __name__ == &#39;__main__&#39;:
    start_time=time.time()
    urls=[]
    KEYWORD=parse.quote(input("请输入要查询的关键词："))
    for i in range(1,2):    # 爬取一页进行测试
        url=f&#39;https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}&#39;
        urls.append(([url,],None))  # threadpool要求必须这样写
    pool=threadpool.ThreadPool(2)  # 2个线程的线程池
    reque=threadpool.makeRequests(getIndex,urls)    # 创建任务
    for r in reque:
        pool.putRequest(r)  # 向线程池提交任务
    pool.wait() # 等待所有任务执行完毕
    print("共获取{}件商品，获得{}条评论，耗时{}".format(num,comments_num,time.time()-start_time))

Second version:

After testing, there will indeed be no blank page

Further optimization: Get reviews of more than 2 products at the same time

# 不能爬太快！！！不然获取不到评论
from bs4 import BeautifulSoup
import requests
from urllib import parse
import csv,json,re
import threadpool
import time
from jd_mysqldb import Goods,Comments,sess_db
 
headers={
    &#39;user-agent&#39;: &#39;Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36&#39;,
    &#39;Cookie&#39;: &#39;__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918&#39;,
    &#39;Referer&#39;: &#39;https://www.jd.com/&#39;
}
 
num=0   # 商品数量
comments_num=0   # 评论数量
 
# 获取商品信息和SkuId
def getIndex(url):
    session=requests.Session()
    session.headers=headers
    global num
    res=session.get(url,headers=headers)
    print(res.status_code)
    res.encoding=res.apparent_encoding
    soup=BeautifulSoup(res.text,&#39;lxml&#39;)
    items=soup.select(&#39;li.gl-item&#39;)
    for item in items[:2]:  # 爬取2个商品测试
        title=item.select_one(&#39;.p-name a em&#39;).text.strip().replace(&#39; &#39;,&#39;&#39;)
        price=item.select_one(&#39;.p-price strong&#39;).text.strip().replace(&#39;￥&#39;,&#39;&#39;)
        try:
            shop=item.select_one(&#39;.p-shopnum a&#39;).text.strip()   # 获取书籍时查找店铺的方法
        except:
            shop=item.select_one(&#39;.p-shop a&#39;).text.strip()  #   获取其他商品时查找店铺的方法
        link=parse.urljoin(&#39;https://&#39;,item.select_one(&#39;.p-img a&#39;).get(&#39;href&#39;))
        SkuId=re.search(&#39;\d+&#39;,link).group()
        headers[&#39;Referer&#39;] = f&#39;https://item.jd.com/{SkuId}.html&#39;
        headers[&#39;Connection&#39;] = &#39;keep-alive&#39;
        comments_num=getCommentsNum(SkuId,session)
        print(SkuId,title, price, shop, link, comments_num)
        print("开始将商品存入数据库...")
        try:
            IntoGoods(SkuId,title, price, shop, link, comments_num)
        except Exception as e:
            print(e)
            sess_db.rollback()
        num += 1
        print("正在获取评论...")
        # 获取评论总页数
        url1 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10&#39;
        res2 = session.get(url1,headers=headers)
        res2.encoding = res2.apparent_encoding
        json_data = json.loads(res2.text)
        max_page = json_data[&#39;maxPage&#39;]  # 经测试最多可获取100页评论，每页10条
        print("{}评论共{}页".format(SkuId,max_page))
        if max_page==0:
            IntoComments(SkuId,&#39;0&#39;)
        else:
            for i in range(0, max_page):
                # 使用此链接获取评论得到的为json格式
                url2 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
                # 使用此链接获取评论得到的非json格式，需要提取
                # url2_2=f&#39;https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
                print("开始获取第{}页评论:{}".format(i+1,url2) )
                getComments(session,SkuId,url2)
                time.sleep(1)
 
# 获取评论总数量
def getCommentsNum(SkuId,sess):
    url=f&#39;https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}&#39;
    res=sess.get(url)
    try:
        res.encoding=res.apparent_encoding
        json_data=json.loads(res.text)  # json格式转为字典
        num=json_data[&#39;CommentsCount&#39;][0][&#39;CommentCount&#39;]
        return num
    except:
        return &#39;Error&#39;
 
# 获取评论
def getComments(sess,SkuId,url2):
    global comments_num
    res2 = sess.get(url2)
    res2.encoding=res2.apparent_encoding
    json_data=res2.text
    &#39;&#39;&#39;
    # 如果用url2_2需要进行如下操作提取json
    start = res2.text.find(&#39;jQuery9287224(&#39;) + len(&#39;jQuery9287224(&#39;)
    end = res2.text.find(&#39;);&#39;)
    json_data=res2.text[start:end]
    &#39;&#39;&#39;
    dict_data = json.loads(json_data)
    comments=dict_data[&#39;comments&#39;]
    for item in comments:
        comment=item[&#39;content&#39;].replace(&#39;\n&#39;,&#39;&#39;)
        # print(comment)
        comments_num+=1
        try:
            IntoComments(SkuId,comment)
        except Exception as e:
            print(e)
            sess_db.rollback()
 
# 商品信息入库
def IntoGoods(SkuId,title, price, shop, link, comments_num):
    goods_data=Goods(
        sku_id=SkuId,
        name=title,
        price=price,
        comments_num=comments_num,
        shop=shop,
        link=link
    )
    sess_db.add(goods_data)
    sess_db.commit()
 
# 评论入库
def IntoComments(SkuId,comment):
    comments_data=Comments(
        sku_id=SkuId,
        comments=comment
    )
    sess_db.add(comments_data)
    sess_db.commit()
 
if __name__ == &#39;__main__&#39;:
    start_time=time.time()
    urls=[]
    KEYWORD=parse.quote(input("请输入要查询的关键词："))
    for i in range(1,2):    # 爬取一页进行测试
        url=f&#39;https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}&#39;
        urls.append(([url,],None))  # threadpool要求必须这样写
    pool=threadpool.ThreadPool(2)  # 2个线程的线程池
    reque=threadpool.makeRequests(getIndex,urls)    # 创建任务
    for r in reque:
        pool.putRequest(r)  # 向线程池提交任务
    pool.wait() # 等待所有任务执行完毕
    print("共获取{}件商品，获得{}条评论，耗时{}".format(num,comments_num,time.time()-start_time))

Third edition:

. . . . No, a blank page appears again

# 不能爬太快！！！不然获取不到评论
from bs4 import BeautifulSoup
import requests
from urllib import parse
import csv,json,re
import threadpool
import time
from jd_mysqldb import Goods,Comments,sess_db
 
headers={
    &#39;user-agent&#39;: &#39;Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36&#39;,
    &#39;Cookie&#39;: &#39;__jdv=76161171|baidu|-|organic|%25E4%25BA%25AC%25E4%25B8%259C|1613711947911; __jdu=16137119479101182770449; areaId=7; ipLoc-djd=7-458-466-0; PCSYCityID=CN_410000_0_0; shshshfpa=07383463-032f-3f99-9d40-639cb57c6e28-1613711950; shshshfpb=u8S9UvxK66gfIbM1mUNrIOg%3D%3D; user-key=153f6b4d-0704-4e56-82b6-8646f3f0dad4; cn=0; shshshfp=9a88944b34cb0ff3631a0a95907b75eb; __jdc=122270672; 3AB9D23F7A4B3C9B=SEELVNXBPU7OAA3UX5JTKR5LQADM5YFJRKY23Z6HDBU4OT2NWYGX525CKFFVHTRDJ7Q5DJRMRZQIQJOW5GVBY43XVI; jwotest_product=99; __jda=122270672.16137119479101182770449.1613711948.1613738165.1613748918.4; JSESSIONID=C06EC8D2E9384D2628AE22B1A6F9F8FC.s1; shshshsID=ab2ca3143928b1b01f6c5b71a15fcebe_5_1613750374847; __jdb=122270672.5.16137119479101182770449|4.1613748918&#39;,
    &#39;Referer&#39;: &#39;https://www.jd.com/&#39;
}
 
num=0   # 商品数量
comments_num=0   # 评论数量
 
# 获取商品信息和SkuId
def getIndex(url):
    global num
    skuids=[]
    session=requests.Session()
    session.headers=headers
    res=session.get(url,headers=headers)
    print(res.status_code)
    res.encoding=res.apparent_encoding
    soup=BeautifulSoup(res.text,&#39;lxml&#39;)
    items=soup.select(&#39;li.gl-item&#39;)
    for item in items[:3]:  # 爬取3个商品测试
        title=item.select_one(&#39;.p-name a em&#39;).text.strip().replace(&#39; &#39;,&#39;&#39;)
        price=item.select_one(&#39;.p-price strong&#39;).text.strip().replace(&#39;￥&#39;,&#39;&#39;)
        try:
            shop=item.select_one(&#39;.p-shopnum a&#39;).text.strip()   # 获取书籍时查找店铺的方法
        except:
            shop=item.select_one(&#39;.p-shop a&#39;).text.strip()  #   获取其他商品时查找店铺的方法
        link=parse.urljoin(&#39;https://&#39;,item.select_one(&#39;.p-img a&#39;).get(&#39;href&#39;))
        SkuId=re.search(&#39;\d+&#39;,link).group()
        skuids.append(([SkuId,session],None))
        headers[&#39;Referer&#39;] = f&#39;https://item.jd.com/{SkuId}.html&#39;
        headers[&#39;Connection&#39;] = &#39;keep-alive&#39;
        comments_num=getCommentsNum(SkuId,session)  # 评论数量
        print(SkuId,title, price, shop, link, comments_num)
        print("开始将商品存入数据库...")
        try:
            IntoGoods(SkuId,title, price, shop, link, comments_num)
        except Exception as e:
            print(e)
            sess_db.rollback()
        num += 1
    print("开始获取评论并存入数据库...")
    pool2=threadpool.ThreadPool(3)   # 可同时获取3个商品的评论
    task=threadpool.makeRequests(getComments,skuids)
    for r in task:
        pool2.putRequest(r)
    pool2.wait()
 
# 获取评论
def getComments(SkuId,sess):
    # 获取评论总页数
    url1 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page=0&pageSize=10&#39;
    res2 = sess.get(url1, headers=headers)
    res2.encoding = res2.apparent_encoding
    json_data = json.loads(res2.text)
    max_page = json_data[&#39;maxPage&#39;]  # 经测试最多可获取100页评论，每页10条
    print("{}评论共{}页".format(SkuId, max_page))
    if max_page == 0:
        IntoComments(SkuId, &#39;0&#39;)
    else:
        for i in range(0, max_page):
            # 使用此链接获取评论得到的为json格式
            url2 = f&#39;https://club.jd.com/comment/productPageComments.action?productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
            # 使用此链接获取评论得到的非json格式，需要提取
            # url2_2=f&#39;https://club.jd.com/comment/productPageComments.action?callback=jQuery9287224&productId={SkuId}&score=0&sortType=5&page={i}&pageSize=10&#39;
            print("开始获取第{}页评论:{}".format(i + 1, url2))
            getComments_one(sess, SkuId, url2)
            time.sleep(1)
 
# 获取评论总数量
def getCommentsNum(SkuId,sess):
    url=f&#39;https://club.jd.com/comment/productCommentSummaries.action?referenceIds={SkuId}&#39;
    res=sess.get(url)
    try:
        res.encoding=res.apparent_encoding
        json_data=json.loads(res.text)  # json格式转为字典
        num=json_data[&#39;CommentsCount&#39;][0][&#39;CommentCount&#39;]
        return num
    except:
        return &#39;Error&#39;
 
# 获取单个评论
def getComments_one(sess,SkuId,url2):
    global comments_num
    res2 = sess.get(url2)
    res2.encoding=res2.apparent_encoding
    json_data=res2.text
    &#39;&#39;&#39;
    # 如果用url2_2需要进行如下操作提取json
    start = res2.text.find(&#39;jQuery9287224(&#39;) + len(&#39;jQuery9287224(&#39;)
    end = res2.text.find(&#39;);&#39;)
    json_data=res2.text[start:end]
    &#39;&#39;&#39;
    dict_data = json.loads(json_data)
    comments=dict_data[&#39;comments&#39;]
    for item in comments:
        comment=item[&#39;content&#39;].replace(&#39;\n&#39;,&#39;&#39;)
        # print(comment)
        comments_num+=1
        try:
            IntoComments(SkuId,comment)
        except Exception as e:
            print(e)
            print("rollback！")
            sess_db.rollback()
 
# 商品信息入库
def IntoGoods(SkuId,title, price, shop, link, comments_num):
    goods_data=Goods(
        sku_id=SkuId,
        name=title,
        price=price,
        comments_num=comments_num,
        shop=shop,
        link=link
    )
    sess_db.add(goods_data)
    sess_db.commit()
 
# 评论入库
def IntoComments(SkuId,comment):
    comments_data=Comments(
        sku_id=SkuId,
        comments=comment
    )
    sess_db.add(comments_data)
    sess_db.commit()
 
if __name__ == &#39;__main__&#39;:
    start_time=time.time()
    urls=[]
    KEYWORD=parse.quote(input("请输入要查询的关键词："))
    for i in range(1,2):    # 爬取一页进行测试
        url=f&#39;https://search.jd.com/Search?keyword={KEYWORD}&wq={KEYWORD}&page={i}&#39;
        urls.append(([url,],None))  # threadpool要求必须这样写
    pool=threadpool.ThreadPool(2)  # 2个线程的线程池
    reque=threadpool.makeRequests(getIndex,urls)    # 创建任务
    for r in reque:
        pool.putRequest(r)  # 向线程池提交任务
    pool.wait() # 等待所有任务执行完毕
    print("共获取{}件商品，获得{}条评论，耗时{}".format(num,comments_num,time.time()-start_time))

The above is the detailed content of How does Python crawl JD product information and comments and store them into MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Statement

This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

MySQL: How to Add a User RemotelyMay 12, 2025 am 12:10 AM

ToaddauserremotelytoMySQL,followthesesteps:1)ConnecttoMySQLasroot,2)Createanewuserwithremoteaccess,3)Grantnecessaryprivileges,and4)Flushprivileges.BecautiousofsecurityrisksbylimitingprivilegesandaccesstospecificIPs,ensuringstrongpasswords,andmonitori

The Ultimate Guide to MySQL String Data Types: Efficient Data StorageMay 12, 2025 am 12:05 AM

TostorestringsefficientlyinMySQL,choosetherightdatatypebasedonyourneeds:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseTEXTforlong-formtextcontent.4)UseBLOBforbinarydatalikeimages.Considerstorageov

MySQL BLOB vs. TEXT: Choosing the Right Data Type for Large ObjectsMay 11, 2025 am 12:13 AM

When selecting MySQL's BLOB and TEXT data types, BLOB is suitable for storing binary data, and TEXT is suitable for storing text data. 1) BLOB is suitable for binary data such as pictures and audio, 2) TEXT is suitable for text data such as articles and comments. When choosing, data properties and performance optimization must be considered.

MySQL: Should I use root user for my product?May 11, 2025 am 12:11 AM

No,youshouldnotusetherootuserinMySQLforyourproduct.Instead,createspecificuserswithlimitedprivilegestoenhancesecurityandperformance:1)Createanewuserwithastrongpassword,2)Grantonlynecessarypermissionstothisuser,3)Regularlyreviewandupdateuserpermissions

MySQL String Data Types Explained: Choosing the Right Type for Your DataMay 11, 2025 am 12:10 AM

MySQLstringdatatypesshouldbechosenbasedondatacharacteristicsandusecases:1)UseCHARforfixed-lengthstringslikecountrycodes.2)UseVARCHARforvariable-lengthstringslikenames.3)UseBINARYorVARBINARYforbinarydatalikecryptographickeys.4)UseBLOBorTEXTforlargeuns

See all articles